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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

jekstein

New Member
Joined
Aug 31, 2006
Messages
18
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

New Member
Joined
Aug 31, 2006
Messages
18

ADVERTISEMENT

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

jekstein

New Member
Joined
Aug 31, 2006
Messages
18
I need it to unhide rows if someone adds a value to the sheet they're linked to
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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
Top