MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need Help!! - How to hide rows based upon specific criteria


Posted by JC on November 06, 2001 12:20 PM

I'm looking for a way to hide rows if certain criteria is met....for example if the value of a certain cell is zero, or blank. Any thoughts? I know this can't be done with conditional formating, but maybe with some simple VBA...I'm not a VBA expert, so please explain in a bit of detail if you have any ideas...THANKS!!


Posted by Mark W. on November 06, 2001 12:27 PM

Use AutoFilter (nt)

Posted by Barrie Davidson on November 06, 2001 12:36 PM

If you must use VBA,

You can use this code.

Sub HideRows()
' Written by Barrie Davidson
For Each cell In Range("A1:A" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
Select Case cell.Value
Case Is = 0
cell.EntireRow.Hidden = True
Case Is = ""
cell.EntireRow.Hidden = True
End Select
Next cell
End Sub

This code looks in column A for values of 0 or "" and hides those rows. If you want a different column, just change the line that reads

For Each cell In Range("A1:A" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)

and put in the appropriate column.

Regards,
Barrie
Barrie Davidson

Posted by JC on November 07, 2001 8:39 AM

Re: Use AutoFilter (nt)


Can you expand on AutoFilter?

Posted by Mark W. on November 07, 2001 10:03 AM

Re: Use AutoFilter (nt)

Take a look at the the Excel Help topic for
"Display a subset of rows in a list by using
filters".