Ways to optimize VBA loop

[Paul_Hossler]

Just to be clear, I don't think I can use any sort of loop. I need it to grab ALL of the columns containing an "X" in row 1 and hide the entire column.

1. I think you DO need a loop

2. That's what my little macro in post #3 does -- hide any column with an "X" in row 3 (from your post #1) -- this version of your requirements (post #6) uses row 1, so I changed my macro

3.My test data has about 300K cells in one worksheet, all cells with formulas, including a VLookup, and runs under a second (deleted lots of rows to fit the upload)

My second has 1200 columns and still runs under a second (I don't think the number of rows has any effect, other than possible formula calculations)

4. If you real data has a 800 column by 400 row square that is filled with VLOOKUP formulas, not to mention the 90+ tabs in the spreadsheet (approx. 30M cells) AND you want to run this on every sheet (you didn't tell us that), then you might change your approach.

I really think that there is something else going on in your workbook. Do you use workbook or worksheet events?

 
Option Explicit

Sub HideColumns()
Dim maxCol As Long, iCol As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

With ActiveSheet
    
    maxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
    For iCol = 1 To maxCol
        If .Cells(1, iCol).Value = "X" Then .Columns(iCol).Hidden = True
    Next iCol
End With

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
 
Sub UnHideColumns()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

ActiveSheet.Columns.Hidden = False
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

5. I'd suggest building the row 1 logic for your formula

=IF(OR(D2>TODAY()+4,D2<TODAY()-13),"X","")

into the macro and not use the "X" flag -- just seems cleaner to me