VBA to hide columns based on a formula

[Sunny London]

Hi

I got the format of the VBA code below from a previous answer on the message board, but it's not working and I can't see what I'm doing wrong. I've searched for other answers too but can't find one that works. I'm a newbie to VBA and learning from sites like this.

On my 'Report' worksheet in cells I1 to Z1 the formula returns "" if there's no data in that month or "1" to "18" depending on which month it is. e.g. if it's a 12 month accounting period then 6 of the columns will have no data in them and will have "" in row 1, the other columns will return 1 - 12 in row 1; if it's a 15 month accounting period then 3 of the columns will have no data in them and "" in row 1 and the other columns will return 1 - 15.

The VBA code below is part of a longer macro on my 'Data' worksheet. The rest of the code works fine and I just wanted to add this bit to hide the blank columns on the 'Report' page.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculate
With Sheets("Report")
Dim i As Integer
If Intersect(Target, Range("I1:Z1")) Is Nothing Then
Else
For i = 9 To 26
If Cells(1, i).Value = "" Then
Columns(i).Hidden = True
ElseIf Cells(1, i).Value = ">0" Then
Columns(i).EntireColumn.Hidden = False
End If
Next i
End If
End With
[/VBA]

Thanks in advance.

 

[VoG]

Try this instead

Private Sub Worksheet_Calculate()
Dim i As Long
For i = 9 To 26
    Columns(i).Hidden = Cells(1, i).Value = ""
Next i
End Sub

 

[Sunny London]

Thanks for your reply.

I must still be doing something wrong - I tried it in my existing 'Data' Private Sub and created a new Private Sub Worksheet_Calculate() in the 'Data' worksheet and tried it as Private Sub Worksheet_Calculate() in the 'Report' worksheet. Couldn't get it to work in any of those.

I know it's difficult to guess what I might be doing wrong, but have you any other suggestions?

Thanks.

 

[VoG]

It works here. You need to right click the sheet tab, select View Code and paste in the code.

 

[Sunny London]

Hi

Just tried again, substituting 0 in the formula instead of "" and putting the macro in the 'Report' worksheet and it worked!

Thank you so much, I've spent ages trawling the message boards and trying different options. Your code is simple and works!