Macro to hide rows in case values in all cells are zero

[skyrockettes2]

Hi there,

I have a data file ranging from B8 until AI77 (headers from B7:AI7), in case each cell in a row is zero or blank (e.g. B8=C8=D8=E8:AI8= 0) the row needs to be hidden. A sum function cannot be used, since I have both positive and negative values in the cells.

Furthermore, columns H, O, V and AC always have blanks. The cells in the other columns are either zero or any possible number (positive or negative). So basically, if in a row there's somewhere a cell that contains a number other than zero, it needs to remain visible. Otherwise, it can be hidden.

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]

Row/Column​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD]

-2​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD="align: center"]0[/TD]
[TD]

1​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD]

-1​

[/TD]
[TD="align: center"][/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

6​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD]

1​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

-4​

[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD]

0​

[/TD]
[/TR]
</TBODY>[/TABLE]

In this case only row 10 should be hidden.

Is there anyone who can help me with a macro? I would really appreciate your time and effort! Many thanks!

FYI, I'm using Excel 2007, I don't know if that matters...

 

[drsarao]

Welcome to Board!
Try this:

Sub HideRows()
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last row in column B
lc = 35 'column AI
For r = 8 To lr  'start at row 8
    allzero = True
    For c = 2 To lc  'start at column B
        If Cells(r, c) <> 0 Then allzero = False: Exit For
    Next c
    If allzero Then Rows(r).Hidden = True
Next r
End Sub

 

[skyrockettes2]

Fantastic, thank you so much drsarao!!

I actually have a follow-up question. I have the above described data file for several regions in seperate tabs; the layout and formulas are all completely the same. The only thing that is different is the name of the region (given in cell A3) that is used to filter the input data. Now I've combined these 8 regional tabs into 1 tab, where I included a dropdown box in cell A3, so you can choose the region you want to look at. I still want to use the hide rows macro, however is it possible that it will automatically re-run in case I change the region in cell A3. (Because each region has a different number of rows that need to be hidden)

 

[drsarao]

In worksheet module, add this code for selection change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 1 Then Call HideRows
End Sub

 

[skyrockettes2]

I'm sorry I don't really understand where I should add that piece of code...
For the region selection I didn't write a code, but just used the button Data Validation

 

[skyrockettes2]

So this is what I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 1 Then Call HideRows
End Sub

Sub HideRows()
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last row in column B
lc = 35 'column AI
For r = 8 To lr 'start at row 8
allzero = True
For c = 2 To lc 'start at column B
If Cells(r, c) <> 0 Then allzero = False: Exit For
Next c
If allzero Then Rows(r).Hidden = True
Next r
End Sub

Sub Add_Drop_Down_Menu_Cell()
With Range("A3").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=Regions"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub


But it will only hide rows the first time I click on cell A3. When I click on a different region in the dropdown list, it doesn't re-run the hide rows macro. Rows that were previously hidden remain hidden, while with the new region they should actually be shown.

 

[drsarao]

Modify HideRows:

Sub HideRows()
lr = Cells(Rows.Count, "B").End(xlUp).Row 'last row in column B
lc = 35 'column AI
[COLOR="#FF0000"]Rows.Hidden = False  'unhide ALL rows[/COLOR]
For r = 8 To lr  'start at row 8
    allzero = True
    For c = 2 To lc  'start at column B
        If Cells(r, c) <> 0 Then allzero = False: Exit For
    Next c
    If allzero Then Rows(r).Hidden = True
Next r
End Sub

 

[skyrockettes2]

Thank you!! That works. The only thing is, when I've selected a different region, I need to click on a different cell and then just click on A3 again before it performs the code. Is there anyway that the code will immediately run when a select a different region from the dropdown list?