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.

 

[Sunny London]

Hi Wigi

Thanks for your help. I'm new to VBA so don't know how to use your event code instructions. At the moment I need things spelt out in more detail if you could (with the full macro code written in so I can copy and paste it). I'm learning VBA and sumitting queries to the forum is helping me to understand it more but I need the answers to be idiot-proof for me to be able to make them work.

 

[wigi]

Then it'd be better to take a few steps back, and read about the subject.

For instance:

http://www.ozgrid.com/VBA/run-macros-change.htm

A Worksheet_Activate event code is similar - again, there's lots of information on the web and this forum if you use the Search function.

 

[Sunny London]

Thanks for the link. I've tried the following code, but it's not working and even with the link you sent me I don't know enough to know how to correct it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim firstRow#, rng As Range, cel As Range
firstRow = 1
If Target.Address(False, False) = "A1" Then
Set rng = Cells(firstRow, "A").Resize(1, 46)
For Each cel In rng
If cel = "0" Or cel = "" Then cel.EntireColumn.Hidden = True
Next
For Each cel In rng
If cel <> "0" And cel <> "" Then cel.EntireColumn.Hidden = False
Next
End If
End Sub

 

[wigi]

Sorry but if you read an article, reply to my post and tried codes, all on 6 minutes, I do not think this is the way to go.

Also, I said you earlier that the worksheet change event must be on another sheet (namely, the sheet where the cell is changed for input). I do not see anything related to different sheets in your code.

 

[Sunny London]

Hi Wigi

I don't know how to relate the macro to different sheets. That's why I'm asking for help. I read the link you sent, it enabled me to get as far as I did, but it didn't cover the specifics of my question.

I need someone to suggest some better code. I'll be able to learn from that.

Thanks

 

[wigi]

I don't know how to relate the macro to different sheets.

No problem, just read a few of the most recent topics on the board and you will find plenty of code to reference difference cells, sheets, workbooks and so on.

 

[Sunny London]

Sorry Wigi, I've looked through the recent posts and none of them has my exact problem. I don't know enough about VBA to adapt the replies to those posts to find a solution.

Can you or anyone else suggest some code that would help?

Thanks