VBA HIde/Unhide Columns Based on Value

[gsimons85]

Good evening

I'm trying to hide some columns based on an IF statement in row. I used a previous post (http://www.mrexcel.com/forum/excel-...ications-hide-unhide-columns-based-value.html) and got the following formula to hide "0" :

Sub Macro1()

Dim i As Long

Application.ScreenUpdating = False

For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) = 0 Then Cells(1, i).EntireColumn.Hidden = True
Next i

Application.ScreenUpdating = True

End Sub

This works fine if my data set starts in A1.

I'd like to edit this formula to start in a different row (e.g. Row 5 or Row 87) and to look for a different value besides 0 (e.g. A or 3).

I'm sure this is an easy fix, but I'm not the brightest in VBA terms

Thanks for the assistance

 

[Shrikant]

structure for Cell argument is

Cells(Row number, Column number)

so in above code you should replace Cell(1, column number) with Cell (Your row number 5 or 87, column number)

 

[xrichardx]

Good evening

I'm trying to hide some columns based on an IF statement in row. I used a previous post (http://www.mrexcel.com/forum/excel-...ications-hide-unhide-columns-based-value.html) and got the following formula to hide "0" :

Sub Macro1()

Dim i As Long

Application.ScreenUpdating = False

For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) = 0 Then Cells(1, i).EntireColumn.Hidden = True
Next i

Application.ScreenUpdating = True

End Sub

This works fine if my data set starts in A1.

I'd like to edit this formula to start in a different row (e.g. Row 5 or Row 87) and to look for a different value besides 0 (e.g. A or 3).

I'm sure this is an easy fix, but I'm not the brightest in VBA terms

Thanks for the assistance

Try:

Starts with Row 5, with value "A"

[COLOR=#ff0000]For i = Cells(5, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) = "A" Then Cells(1, i).EntireColumn.Hidden = True
Next i

[/COLOR]Starts with Row 87, with value 3

[COLOR=#ff0000]For i = Cells(87, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i) = 3 Then Cells(1, i).EntireColumn.Hidden = True
Next i

[/COLOR]Change the code accordingly to your requirements.

Cheers
Rich