VBA to show/hide columns depending on cell value in first row of column

[Sunny London]

I have up to 18 columns of data. The first row contains a VLOOKUP formula which returns a blank, 0, 1, 2, 3 etc up to 18. If there are only, say, 10 columns that I want to be visible, they will be numbered 1 - 10 and the other 8 columns will have either a blank or a 0 in the first row.

What is the VBA code that can look at the individual cells in the first row of the data and if it's a blank or 0 hide that column?

Thanks.

 

[Boller]

Dim firstRow#, rng As Range, cel As Range
firstRow = 1
Set rng = Cells(firstRow, "A").Resize(1, 18)
For Each cel In rng
    If cel = "0" Or cel = "" Then cel.EntireColumn.Hidden = True
Next

 

[wigi]

Or:

Sub tst()
    On Error Resume Next
    Rows(1).SpecialCells(-4123, 1).EntireColumn.Hidden = True
End Sub

For testing purposes, I put in cells A1:Z1:

=IF(COLUMN()<=18,COLUMN(),"")

Wigi

 

[Sunny London]

I can't get either solution to work. I right clicked on the tab and selected View Code then copied and pasted in your code but nothing happens, the columns with 0 or blank in the top row stay visible.

What does the
SpecialCells(-4123, 1)
line of VBA do?

Thanks

 

[Boller]

I can't get either solution to work. I right clicked on the tab and selected View Code then copied and pasted in your code but nothing happens, the columns with 0 or blank in the top row stay visible.

What does the
SpecialCells(-4123, 1)
line of VBA do?

Thanks

I've just tested my code and it works for me.

Try stepping through the code via F8 and see what it does.

 

[Sunny London]

ok, I see. I have to run the macro to make it work. Is there a way of making it run automatically so when the numbers in row A change (they depend on the value of a cell in another worksheet so may be 0 or may be another value) then the columns will hide automatically without the user having to run the macro?

Also, when the numbers change back and are no longer 0, the columns currently don't unhide. How can I make them unhide automatically?

Thanks

 

[wigi]

I can't get either solution to work. I right clicked on the tab and selected View Code then copied and pasted in your code but nothing happens, the columns with 0 or blank in the top row stay visible.

Normally you would paste this code in a normal module, not in the Sheet's code.

What formula do you have in cell A1?

 

[wigi]

ok, I see. I have to run the macro to make it work. Is there a way of making it run automatically so when the numbers in row A change (they depend on the value of a cell in another worksheet so may be 0 or may be another value) then the columns will hide automatically without the user having to run the macro?

Also, when the numbers change back and are no longer 0, the columns currently don't unhide. How can I make them unhide automatically?

Thanks

Use event codes like:

Worksheet_Change (on the sheet where the changes in the cells are made)
Worksheet_Deactivate (on the sheet where the changes in the cells are made)
Worksheet_Activate (on the sheet where the columns are shown or not)

Or a simple button for the user to click on. Then you do not depend on events.

You will also need the code to show the columns again.

Wigi

 

[Sunny London]

I want to automatically hide cells D1 to F1, H1 to Z1 and AB1 to AS1 on my "Data" worksheet if there's a blank in their first line. The formula in those cells is:
=IF(Menus!$Y$2<0,"",Menus!$Y$2)
Y2 is a date that can change each time the spreadsheet is used and sometimes there's no date which is when I want the columns to be hidden.

I've just put "100" in cell A1 to stop it being hidden when the macro runs.

Normally row A is hidden to keep the spreadsheet tidy.

Is there some way I can automate the showing/hiding of those columns depending on the value of Y2 on my "Menus" worksheet?

 

[wigi]

I want to automatically hide cells D1 to F1, H1 to Z1 and AB1 to AS1 on my "Data" worksheet if there's a blank in their first line. The formula in those cells is:
=IF(Menus!$Y$2<0,"",Menus!$Y$2)
Y2 is a date that can change each time the spreadsheet is used and sometimes there's no date which is when I want the columns to be hidden.

I've just put "100" in cell A1 to stop it being hidden when the macro runs.

Normally row A is hidden to keep the spreadsheet tidy.

Is there some way I can automate the showing/hiding of those columns depending on the value of Y2 on my "Menus" worksheet?

All this explanation confirms that my code is still valid to be used and will work if applied correctly.

If speaking about the Worksheet_Change event like above, it will be based on cell Y2 on sheet Menus.

For the rest, see my texts in the posts above.