Need for speed

[Gregg Wolin]

I am using some VBA to show and hide the visibility of groups of rows and columns, creating an accordion effect. I have a really fast machine but the code below takes between 2.5 and 4 seconds to run.

Public Sub NavButton_Predev()
 Application.ScreenUpdating = False
 
    Columns("f:Ae").Hidden = True
    Columns("af:Aq").Hidden = True
    Columns("af:Aq").Hidden = True
    Columns("as:xfd").Hidden = True
  
    Rows("6:23").Hidden = False
  
    Rows("24:25").Hidden = False
    Rows("26:40").Hidden = True    'Hides offsite cost details
  
    Rows("41:42").Hidden = False
    Rows("43:66").Hidden = True    'Hides onsite cost details
  
    Rows("41:42").Hidden = False 
    Rows("43:66").Hidden = True   'Hides onsite cost header
  
    Rows("67:68").Hidden = False
    Rows("69:80").Hidden = True    'Hides amenities cost details
  
    Rows("81:82").Hidden = False
    Rows("83:92").Hidden = True    'Hides Total details
      
    Rows("93:94").Hidden = False
    Rows("95:104").Hidden = True    'Hides Ownership cost details
  
    Rows("105:106").Hidden = False
    Rows("107:118").Hidden = True    'Hides HOA Subsidy details
  
    Rows("119:120").Hidden = False
    Rows("121:132").Hidden = True    'Hides G&A details
  
    Rows("133:134").Hidden = False
    Rows("135:140").Hidden = True    'Hides Contingency details
  
    Rows("141:142").Hidden = False
    Rows("143:192").Hidden = True    'Hides Reimbursements details
      
    Columns("af:ag").ColumnWidth = 2
    Columns("ah").ColumnWidth = 32
    Columns("ai:aj").ColumnWidth = 15
    Columns("ak:al").ColumnWidth = 15
    Columns("am:am").ColumnWidth = 40
      
    Range("ah11").Select
  
    Application.ScreenUpdating = True
 
 End sub

Does anyone have any ideas how to speed this up?

 

[vletm]

Gregg Wolin
eg check Your 'columns' ... which columns need to hide (You have doubles, isn't there from f:aq, as:xfd ?)
as well with 'rows' ... there are doubles, rows 6:142 to false and after that needed rows true

 

[salim hasan]

Code correction

Option Explicit

Sub test()
    Rows("6:192").Hidden = False
    Dim arr(1 To 8),  n%
    arr(1) = "26:40": arr(2) = "43:66": arr(3) = "96:80"
    arr(4) = "83:92": arr(5) = "95:104": arr(6) = "107:118"
    arr(7) = "121:132": arr(8) = "143:192"
        
        For n = LBound(arr) To UBound(arr)
            Rows(arr(n)).Hidden = True
        Next
End Sub

 

[Gregg Wolin]

Just saw this. Thanks!

 

[Marc L]

Code correction

arr(1) = "26:40": arr(2) = "43:66": arr(3) = "96:80"

Salim, an Excel basics way to create an array :​

Sub Demo1()
        Dim V
        Application.ScreenUpdating = False
        Rows("6:192").Hidden = False
    For Each V In [{"26:40","43:66","69:80","83:92", "95:104","107:118","121:132","135:140","143:192"}]
        Rows(V).Hidden = True
    Next
        Application.ScreenUpdating = True
End Sub

​

Or using an array VBA function like For Each V In Split("26:40 43:66 69:80 83:92 95:104 107:118 121:132 135:140 143:192") …​

​

And at once without looping :​

Sub Demo2()
    Application.ScreenUpdating = False
    Rows("6:192").Hidden = False
    [A26:A40,A43:A66,A69:A80,A83:A92,A95:A104,A107:A118,A121:A132,A135:A140,A143:A192].EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​