hide colum based on cell value on each worksheets
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. |
hide colum based on cell value on each worksheets
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" skrev i meddelelsen ... 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. |
hide colum based on cell value on each worksheets
On Feb 1, 2:25*am, "Per Jessen" wrote:
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" skrev i ... 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.- Hide quoted text - - Show quoted text - Thanks, it works perfectly. |
All times are GMT +1. The time now is 12:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com