Hiding/Unhiding Columns based on Criteria

[luke1438]

I have attached a copy of my worksheet for reference.
I am trying to hide columns based off of the number in row 2. This number corresponds to the numbers in row 4. The number in row 2 can change; there will be only one number in row 2 and it will always be above its number in row 4. There will be columns that are blank, in leys the issue I am having. I need to hide the 6 previous columns that have numbers in row 4 and unhide the next 6 columns that have numbers in row 4. Because of my basic VBA skills everything I have tried has compiling errors or just simply does not work. I would appreciate any suggestions or guidance.

Thank you
Luke

[figment]

let me make sure i am geting this right. You always need to hide the 6 columns fefor the number in row 2 and the 6 columns after the number. the actual number means nothing. and you only want to hide columns that have information in row 4. if both of these assumptions are correct then the folowing code should work for you

[VBA]Sub Hide_Columns()
Dim a As Integer, b As Integer
Dim counttosix As Byte, marker As Byte
Dim found As Boolean
Columns.Hidden = False
For a = 0 To 255
If Cells(2, a + 1) = Cells(4, a + 1) And Cells(2, a + 1) <> "" Then
marker = a
a = 255
found = True
End If
Next
If found Then
counttosix = 0
b = marker
While counttosix < 6 And b > 0
If Cells(4, b) <> "" Then
Columns(b).Hidden = True
counttosix = counttosix + 1
End If
b = b - 1
Wend
counttosix = 0
b = marker + 2
While counttosix < 6 And b < 256
If Cells(4, b) <> "" Then
Columns(b).Hidden = True
counttosix = counttosix + 1
End If
b = b + 1
Wend
End If
End Sub[/VBA]

[luke1438]

Code:

Sub Hide_Columns()
Dim a As Integer, b As Integer
Dim counttosix As Byte, marker As Byte
Dim found As Boolean
Columns.Hidden = False
For a = 0 To 255
If Cells(2, a + 1) = Cells(4, a + 1) And Cells(2, a + 1) <> "" Then
marker = a
a = 255
found = True
End If
Next
If found Then
counttosix = b
b = marker - 6
While b > 0
If Cells(4, b) <> "" Then
Columns(b).Hidden = True
counttosix = counttosix + 1
End If
b = b - 1
Wend
counttosix = b
b = marker + 9
While b < 256
If Cells(4, b) <> "" Then
Columns(b).Hidden = True
counttosix = counttosix + 1
End If
b = b + 1
Wend
End If
End Sub

[luke1438]

I have attached a copy of my worksheet for reference.
I am trying to hide columns based off of the number in row 2. This number corresponds to the numbers in row 4. The number in row 2 can change; there will be only one number in row 2 and it will always be above its number in row 4. There will be columns that are blank, in leys the issue I am having. I need to hide the 6 previous columns that have numbers in row 4 and unhide the next 6 columns that have numbers in row 4. Because of my basic VBA skills everything I have tried has compiling errors or just simply does not work. I would appreciate any suggestions or guidance.

Thank you
Luke

[luke1438]

Figment,

Thank you for your help, the code works perfect. However, I screwed up. I needed the 6 columns to the left unhiden and the 6 columns to the right unhiden; so there is a total of 13 columns unhiden. I took your code and did some modifications and got it to work the way I needed. I really apppreciate your help and it was good that I screwed up; it forced me to understand your code and learn.
Thanks again
Luke

ps. I think my next learning experience should be how to post on this board a little better. I have posted the modified code just in case someone else needs it.