hide colum based on cell value on each worksheets

  • Thread starter Thread starter Norvascom
  • Start date Start date
N

Norvascom

I have a workbook with several worksheets. I am trying to write a VBA
macro that would look at row 5 to see if any cells indicate "Hide
column". If it does, it would hide all the columns indicating "Hide
column" on this worksheet. Then it would continue the same process to
the next worksheet, and the next one ...

I currently have the macro working for the individual worksheet (see
below), but I would like to have it work to do all the worksheets of
the workbook.

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For a = 5 To 200
If Range("A5").Offset(0, a).Value = "Hide column" Then Range
("A5").Offset(0, a).EntireColumn.Hidden = True
Next a
Application.ScreenUpdating = True

Thanks in advance for your help.
 
Hi

This should do it:

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each sh In ThisWorkbook.Sheets
For a = 5 To 200
If sh.Range("A5").Offset(0, a).Value = "Hide column" Then
sh.Range("A5").Offset(0, a).EntireColumn.Hidden = True
End If
Next a
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Per
 
Hi

This should do it:

Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each sh In ThisWorkbook.Sheets
    For a = 5 To 200
        If sh.Range("A5").Offset(0, a).Value = "Hide column" Then
            sh.Range("A5").Offset(0, a).EntireColumn.Hidden = True
        End If
    Next a
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Regards,
Per

"Norvascom" <[email protected]> skrev i meddelelsen






- Show quoted text -
Click to expand...

Thanks, it works perfectly.
 
Back
Top