Autofilter

[pook_666]

<code>Hi gang - I need some help.....I'm doing an autofilter and want I want is that if the filter doesn't come up with any results, then I want to unfilter. If it does come up with a result, I want to copy the data in column H onto a different sheet.

What currently happens with my below code is that if there is no data when I filter, it ignores my unfilter command and goes straight onto adding "Equipment & Fixtures" and copies the filter header in H1 to sheet "Balance Sheet".

Any ideas?

<code>

Sheets("Data").Columns("C:C").AutoFilter Field:=3, Criteria1:="602"

If Sheets("Data").AutoFilter.Range.Rows.Count < 1 ThenSheets("Data").Columns("C:C").AutoFilter Field:=3
Else

Sheets("Balance Sheet").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = "Equipment & Fixtures"
lrow = Sheets("Data").Range("H" & Rows.Count).End(xlUp).Row
Sheets("Data").Range("H2:H" & lrow).SpecialCells(xlCellTypeVisible).Copy Sheets("Balance Sheet").Cells(Rows.Count, 5).End(xlUp).Offset(1, 0)

End If

</code></code>

 

[Akuini]

Untested, but try replacing this:

If Sheets("Data").AutoFilter.Range.Rows.Count < 1 Then

with:

If Sheets("Data").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then

 

[pook_666]

Perfect! That worked a dream! Thank you

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.