Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Hiding the rows with X in Column Y

written by Mark Rowlinson - Last updated Mar 2005

The following code will hide all rows that don't have a 0 in column C: 

 
Dim lngCol As Long 
Dim strCondition As String 
Dim ws As Worksheet 
Dim r As Range 
lngCol = 3 'set this value To the column number you wish To filter on 
'set the consition which should be the opposite of the actual condition you want To check 
strCondition="<>0" 'i.e. doesn't equal 0 
Set ws = ActiveSheet 'use the activesheet 
With ws.Rows(1) 
    .AutoFilter 'tunr on filter 
    .AutoFilter Field:=lngCol, Criteria1:=strCondition 'filter on condition 
    'save the visible cells 
    Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0), ws.UsedRange) 
    .AutoFilter 'turn off filter 
End With 
'now hide the cells that were visible previously 
r.EntireRow.Hidden = TRUE