::hide rows if value is 0::

jekstein

New Member
Joined
Aug 31, 2006
Messages
18
currently i have my code checking a specific cell and hiding if that cell = 0

example:
Code:
If Sheets("Total Sheet").Range("a7:a7").Select = 0 Then
    Rows("7").EntireRow.Hidden = True
    End If

I want to have my code check a range of cells and then if any of them = 0 i want it to hide the row its associated with.

Can anyone help me/??

THank you in advance
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
This will hide any row in column A that contains a zero.
Code:
Sub DeleteRowsIfZero()
FinalRow = Range("A65536").End(xlUp).Row
    For x = FinalRow To 1 Step -1
        If Cells(x, "A").Value = 0 Then
            Rows(x).EntireRow.Hidden = True
        End If
    Next x
End Sub
 
Upvote 0
Hi jekstein

You didn't say how many cells contains your column range. If it's a big range, autofilter is the most efficient solution. If it's a small range you may prefer to loop through it using the the "For Each" loop (check the help) and test each individual cell.

I post an example with the autofilter. Please adjust for the worksheet and the column range.

Hope this helps
PGC

Code:
Sub DelCells0()
Dim rR As Range, rFilt As Range, wsh As Worksheet

Set wsh = Worksheets("Sheet8")
Set rR = wsh.Range("F1:F50")

wsh.AutoFilterMode = False
rR.AutoFilter Field:=1, Criteria1:="=0"
Set rFilt = rR.SpecialCells(xlCellTypeVisible)
wsh.AutoFilterMode = False
rFilt.EntireRow.Hidden = True
End Sub
 
Upvote 0
basically i have a sheet with several peoples info on it (they each have their own section on the sheet) i want to it to check the range for each person

example:

if sheets("Total Sheet").range(a7:a31) = 0 then hide the cooresponding row.

and the the next section would be range(a35:a59) and so on..
 
Upvote 0
jekstein,

This adjusted code will do what you are asking. It hides every row that has a zero in all of column A.
Code:
Sub HideRowIfZero()
Dim HideRange As Range, c As Range
    Columns(1).EntireRow.Hidden = False
    Set HideRange = Range("A1:A" & _
    Range("A65536").End(xlUp).Row)
    
        For Each c In HideRange
            If c.Value = "0" Then c.EntireRow.Hidden = True
        Next c

End Sub
This works on all of column A, no need to break it into the sections you are talking about.

The previous code I posted also hid rows that had blank cells in column A.

pgc01,
I tried your code and it also hides the top row if it is zero or not.
 
Upvote 0
Hi John

pgc01,
I tried your code and it also hides the top row if it is zero or not.

You are right. My bad. Autofilter always leaves the first row visible (the headers) and so you never include the first row when you get the visible lines. Thanks for pointing out the error.

Anyway I'll not post another version of the code because in this case I think your solution is perfect.

Cheers
PGC
 
Upvote 0
Put your code in the worksheet module you want the code to run in
Right click the Sheet Tab, choose View Code, paste code in the right side panel.
Replacr your macro name with the following name:
"Private Sub Worksheet_Change(ByVal Target As Range)" wthout quotes.

To keep from running the code in a never ending cycle...
Put this line of code just below the first line:
Application.EnableEvents = False
Then at the bottom of your code add:
Application.EnableEvents = True
 
Upvote 0

Forum statistics

Threads
1,217,757
Messages
6,138,430
Members
450,137
Latest member
HANHAN

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