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

#### skyrockettes2

##### New Member
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.

 Row/Column​ B​ C​ D​ E​ F​ G​ H​ I​ J​ 8​ 0 0 0 0 -2​ 0 0 0​ 9​ 0 1​ 0 0 0 -1​ 2​ 0​ 10​ 0 0 0 0 0 0 0 0​ 12​ 6​ 0 0 1​ 0 0 0 3​ 13​ 2​ 2​ -4​ 0 0 0 0 0​

<TBODY> </TBODY>

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...

Last edited:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to Board!
Try this:
Code:
``````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``````

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)

In worksheet module, add this code for selection change:
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 1 Then Call HideRows
End Sub``````

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

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

With Range("A3").Validation
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.

Modify HideRows:
Code:
``````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``````

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?

Replies
9
Views
395
Replies
4
Views
381
Replies
7
Views
1K
Replies
5
Views
138
Replies
2
Views
200

1,211,841
Messages
6,104,304
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back