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

skyrockettes2

New Member
Joined
Jun 29, 2012
Messages
12
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​
0000
-2​
00
0​
9​
0
1​
000
-1​
2​
0​
10​
0000000
0​
12​
6​
00
1​
000
3​
13​
2​
2​
-4​
0000
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top