::hide rows if value is 0::

[jekstein]

currently i have my code checking a specific cell and hiding if that cell = 0

example:

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

 

[Datsmart]

This will hide any row in column A that contains a zero.

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

 

[pgc01]

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

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

 

[jekstein]

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

 

[Datsmart]

jekstein,

This adjusted code will do what you are asking. It hides every row that has a zero in all of column A.

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.

 

[pgc01]

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

 

[jekstein]

AWESOME.. TY That's exactly what i needed. thanks a millllion!!!!!!!!!

 

[jekstein]

how can i make it run if the sheet changes?

 

[jekstein]

I need it to unhide rows if someone adds a value to the sheet they're linked to

 

[Datsmart]

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