Hiding rows automatically based on the value in a cell

  • Thread starter Thread starter Cathy
  • Start date Start date
C

Cathy

Hi,
If anyone has an idea on how to go about this, I would
really appreciate it.

I have a spreadsheet used for various calculations. The
spreadsheet creates a list of the number of needed from a
specific role. The sheet lists all available roles for
each project and then caluculate the number of hours per
role per project. The roles that do not have any
requested hours show up with zeros values. I
currenlty 'grey' them out using conditional logic. The
spreadsheet would be more friendly if I
could 'hide/delete' rows with a zero value.

I am happy with a toggle switch solution, a cell
calculated solution or anything that works.

Thank you
 
this might help. Assign to a button. You probably don't need to sort part

Sub FilterZeros()
Range("A1:A12").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Thank you. I tried your solution and it worked really
well. It was a little slow. Here is another solution
that is really quick.

In the click event of a toggle button call the following
VB module

Sub HideZeros()

'hide rows that have 0
'MyRange is a named range that can be changed by going to
Insert...Name...Define
'on the Excel sheet

Dim iRow
Dim iFirstRow
Dim iLastRow
Dim iColumn

'don't show updates on screen
Application.ScreenUpdating = False

'retrieve range boundaries
iFirstRow = Range("MyRange").Row
iLastRow = Range("MyRange").Row + Range
("MyRange").Rows.Count - 1
iColumn = Range("MyRange").Column

'loop through rows
For iRow = iFirstRow To iLastRow
If Cells(iRow, iColumn).Value = 0 Then
Cells(iRow, iColumn).EntireRow.Hidden = True
Else
Cells(iRow, iColumn).EntireRow.Hidden = False
End If
Next
End Sub
-----Original Message-----
this might help. Assign to a button. You probably don't need to sort part

Sub FilterZeros()
Range("A1:A12").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
Selection.Sort Key1:=Range("A2"),
Click to expand...
Order1:=xlAscending, Header:=xlGuess,
 
Back
Top