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