Macro Hide Columns

[timjohnny]

Hi all, I hope you're all well.

I'm looking for a macro that would hide columns for two conditions:
For a defined range it would always hide every 3rd column, no matter the content. But it would also hide the two columns to the left of any 3rd column if the third column contains a cell = 0. Here's an example using dummy data:

So in this example, I want the macro to always hide the "Ties" columns (but in the original data I wouldnt have a consistent title like "ties" for the macro to use, it would have go by 3rd column starting at column B). And the macro should also hide columns E and F because column G contains two cells = 0 (In the original data, if any cell within range G3:G6 = 0, then all cells in range G3:G6 would be 0, so one could use a sum(G3:G6)=0 criterion if that'd be easier. One could also pick any row/cell within that range, e.g. G3, and check if it is 0.

Thanks a lot for any help!

Best
Tim

 

[maabadi]

Try this:

Sub HideColumns()
Dim i As Long, j As Long, Lr As Long, Lc As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
Lc = Cells(3, Columns.Count).End(xlToLeft).Column
On Error Resume Next
Columns.EntireColumn.Hidden = False
For j = Lc To 2 Step -1
If j Mod 3 = 1 Then
If Application.WorksheetFunction.Min(Range(Cells(2, j), Cells(Lr, j))) = 0 Then
Columns(j - 1).EntireColumn.Hidden = True
Columns(j - 2).EntireColumn.Hidden = True
End If
Columns(j).EntireColumn.Hidden = True
j = j - 2
End If
Next j
End Sub

 

[timjohnny]

Hi maabadi, thanks a lot for responding! Sorry for my own slow response.. had to figure out another excel obstacle. They just never stop, do they? :D In the macro above, is the range to which this applies defined? I would need to be able to make it so that it only applies to a specified range of columns. In this example B:G.

 

[maabadi]

Change this line to last column Number. This:

  Lc = Cells(3, Columns.Count).End(xlToLeft).Column

To

Lc = 7

OR Use this Code:

Sub HideColumns()
Dim i As Long, j As Long, Lr As Long, Lc As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Columns.EntireColumn.Hidden = False
For j = 7 To 4 Step -3
If Application.WorksheetFunction.Min(Range(Cells(2, j), Cells(Lr, j))) = 0 Then
Columns(j - 1).EntireColumn.Hidden = True
Columns(j - 2).EntireColumn.Hidden = True
End If
Columns(j).EntireColumn.Hidden = True
Next j
End Sub

 

[timjohnny]

Thanks! what line of the code defines the starting point? In my original data i need to start in column E (cell E5), so I'll need to define that (i think?). Sorry about the basic questions, I'll get there.

 

[maabadi]

Cells(2, j)

2 shows row 2 then change it to 5.

j = 7  to 4

Shows from Column 7 ( = G) to Column 4 ( = D) Criteria column to hide & unhide column B & C.

 

[timjohnny]

Hi Maabadi,

unfortunately, this didnt work for me. I suspect that I made a mistake when I changed some of the code to fit my range... This would be a more accurate depiction of my original data:

The data continues to column no. 2401, so I've changed the macro as follows:

Sub HideColumns()
Dim i As Long, j As Long, Lr As Long, Lc As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Columns.EntireColumn.Hidden = False
For j = 2401 To 7 Step -3
If Application.WorksheetFunction.Min(Range(Cells(5, j), Cells(Lr, j))) = 0 Then
Columns(j - 1).EntireColumn.Hidden = True
Columns(j - 2).EntireColumn.Hidden = True
End If
Columns(j).EntireColumn.Hidden = True
Next j
End Sub

Can you spot the mistake?

Thanks!

 

[timjohnny]

turns out I'm a foool, my cursor was in the wrong place when I ran the code. Thanks so much for your help Maabadi. You're a hero

 

[maabadi]

my cursor was in the wrong place when I ran the code.

You're Welcome & Thanks for Feedback.

For don't see error at the future add sheet name to code, to can do it run from each sheet at active Workbook.
Change Sheet1 at code to your Data Sheet name.
Then Code can change to this:

Sub HideColumns()
Dim i As Long, j As Long, Lr As Long, Ws as Worksheet
Set Ws = Sheets("Sheet1")
With Ws
Lr = .Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
.Columns.EntireColumn.Hidden = False
For j = 2401 To 7 Step -3
If Application.WorksheetFunction.Min(Range(.Cells(5, j), .Cells(Lr, j))) = 0 Then
.Columns(j - 1).EntireColumn.Hidden = True
.Columns(j - 2).EntireColumn.Hidden = True
End If
.Columns(j).EntireColumn.Hidden = True
Next j
End With
End Sub