VBA HIde/Unhide Columns Based on Value

gsimons85

New Member
Joined
Apr 1, 2014
Messages
32
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 :eek:

Thanks for the assistance
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Shrikant

Active Member
Joined
Dec 28, 2010
Messages
284
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

Board Regular
Joined
Apr 24, 2013
Messages
67
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 :eek:

Thanks for the assistance


Try:

Starts with Row 5, with value "A"
Code:
[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
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,228
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top