Using macros to filter

G

Guest

Hi,

I just recorded a macro that uses the autfilter to filter
so only blank cells are shown in a column. The text that
is in the VB editor when you step into the macro is:

Selection.AutoFilter Field:=14, Criteria1:="="
Range("A1").Select

Does anyone know how to adapt this so that the autofilter
isn't needed? And also how to reverse it, like a show all.
But I want the show all not to just undo the above, if I
write more macro I want it to undo any that are on?
 
F

Frank Kabel

Hi
try something like the following:
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").value="criteria" Then
Rows(RowNdx).hidden = True
End If
Next RowNdx
End Sub
 
F

Frank Kabel

Hi
no this does just hide the rows (if you want to filter you'll have to
use Autofilter). For column N use
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "N").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "N").value<>"" Then
Rows(RowNdx).hidden = True
End If
Next RowNdx
End Sub
 
Top