How to speed up VBA Code

[cknnugget]

The code hides all unneeded columns except for the columns that need viewed. Dates are in row 3. The user selects the Date From dropdown in cell "H3". VBA then loops through the columns and hides all cloumns except for the selected columns. The Code runs good; however, it takes almost a minute to complete. Is there a way to speed up the process? Would Dictionary object work? I know about Dictionary Object but have not learned how to use it yet.

Sub OrderView()
Cells.EntireColumn.Hidden = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
For Each c In Range("T3:ADM3").Cells
        If (c.Value <> Range("H3")) And (c.Value <> Range("I2")) Then
            c.EntireColumn.Hidden = True
        End If
    Next c
    Range("A:B,H:L,P:P,Q:Q,R:R,S:S").EntireColumn.Hidden = True
    
End Sub

 

[frabulator]

The code hides all unneeded columns except for the columns that need viewed. Dates are in row 3. The user selects the Date From dropdown in cell "H3". VBA then loops through the columns and hides all cloumns except for the selected columns. The Code runs good; however, it takes almost a minute to complete. Is there a way to speed up the process? Would Dictionary object work? I know about Dictionary Object but have not learned how to use it yet.

Sub OrderView()
Cells.EntireColumn.Hidden = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
For Each c In Range("T3:ADM3").Cells
        If (c.Value <> Range("H3")) And (c.Value <> Range("I2")) Then
            c.EntireColumn.Hidden = True
        End If
    Next c
    Range("A:B,H:L,P:P,Q:Q,R:R,S:S").EntireColumn.Hidden = True
   
End Sub

I am sure there is a more elegant way than what you are doing, but just to speed up your code you have provided, you can do the following:

Application.ScreenUpdating

Example (Not tested)

Sub OrderView()
Application.ScreenUpdating = False
Cells.EntireColumn.Hidden = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
For Each c In Range("T3:ADM3").Cells
        If (c.Value <> Range("H3")) And (c.Value <> Range("I2")) Then
            c.EntireColumn.Hidden = True
        End If
    Next c
    Range("A:B,H:L,P:P,Q:Q,R:R,S:S").EntireColumn.Hidden = True
Application.ScreenUpdating = True
End Sub

 

[cknnugget]

Tried Screen updating and no change in speed

 

[kevin9999]

See if the following is any faster. I suspect there may be a lot of background calculations going on when you show all data (assuming a filter is in place) and maybe even some CF as well?

Sub Test()
    Application.Calculation = xlManual
    Cells.EntireColumn.Hidden = False
    
    Dim a, x As String, y As String, i As Long, r As Range
    x = Range("H3").Value2
    y = Range("I2").Value2
    
    a = Range("T3:ADM3").Value2
    For i = LBound(a, 2) To UBound(a, 2)
        If a(1, i) <> x And a(1, i) <> y Then
            If Not r Is Nothing Then
                Set r = Union(r, Columns(i + 19))
            Else
                Set r = Columns(i + 19)
            End If
        End If
    Next i
    
    Set r = Union(r, Union(Columns(1).Resize(, 2), Columns(8).Resize(, 5), Columns(16).Resize(, 4)))
    r.EntireColumn.Hidden = True
    Application.Calculation = xlAutomatic
End Sub

 

[cknnugget]

Works significantly faster except for Line Cells.EntireColumn.Hidden = False . This line seems to slow everything down. Not sure why because it is just unhiding all columns

I ran it as a separate sub, and turned off screen updating. It is slow. On an empty sheet, I hide the same columns and it worked fast.

Sub All()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Cells.EntireColumn.Hidden = False
'Range("A:B").EntireColumn.Hidden = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[kevin9999]

I'd really need to see your actual file before I can offer any further suggestions. Can you share it via Dropbox, Google Drive or similar file sharing sites?