VBA to Hide Column AND Adjacent Column if value of a cell is 0

[David Evans]

Hi Folks -

My Lovely legacy worksheet continues to be the bane of my existence ....

I want to check every other column for a value in Row 97 fwiw .... If that value in the column is zero, I want to hide that column and the one adjacent to it. This is where my ignorance of those COLUMN() and OFFSET() commands gets me - or rather, I don't get them .... am I on the right track with these two?

Thanks for any insight - I'll be back in on my arrival home to receive the fusillade of answers, or to pickup all the buns you've thrown my way!

Thanks Very Much

D

 

[Smallman]

Dave
When you say adjacent what do you mean - to the left or to the right of the cell you are testing.
I put this together so it hide the cell adjacent to the right.

Option Explicit
 
Sub HidePlus()
Dim i As Integer
 
    For i = 1 To Range("IV97").End(xlToLeft).Column Step 2
        Cells(97, i).Resize(, 2).EntireColumn.Hidden = (Cells(97, i) = 0)
    Next i
End Sub

File attached to show workings.

Take care

Smallman

 

[David Evans]

Thanks Smallman - yes I do mean the column to the right of the one being evaluated.

I think its working - I can't tell 100% as the model I thought I'd sent myself seems to have got lost - I'll have to wait til morning.

Thanks again - get on the PayPal thingy so that I can reward you!