custom auto filter

[Ranjit kurian]

Hi

When i do Custome autofillter for 'Mon' in Month Column, and if 'Mon' is not
found then exit sub if found then copy the entire sheet.


example:
Month
Mon
Tue
Wed
Mon

 

[Bob Phillips]

Dim rng As Range
Dim LastRow As Long

Columns(1).AutoFilter
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="Mon"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng.Rows.Count > 1 Then

Cells.Copy
'do something with it
End If

rng.AutoFilter