Solved: when autofilter returns nothing

[philfer]

Hi,

I am using autofilter in code to get a large list of data to only give me certain information which I copy and paste in different sheets.

However some days one or more of the autofilters may return zero results (depending on the data download)

Is there a way to capture is autofilter returns nothing as I set the range as cell A2 to the lastrow (which I have calculated at the beginning of the code) using xlvisiblecells and if it returns nothing it thinks the rest of the blank sheet is the range.

Is there any way to say if the results are zero then do nothing

[Simon Lloyd]

Maybe something along these lines:
[VBA]
Sub Macro1()
Dim rFound As Variant
On Error GoTo Nxt
rFound = Sheets("Sheet1").UsedRange.Find(What:="", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart).Value
Nxt:
If rFound = vbNullString Or rFound = 0 Then
MsgBox "Doing nothing!"
Else
MsgBox "found it "
End If
End Sub
[/VBA]

[herzberg]

I work with Autofilters a lot too. Here's what I use:

[vba] With .AutoFilter.Range
Counter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
If Counter = 1 Then
'Occurs when no records are returned. Exit Sub here or something
End If
End With[/vba]
Basically, I count the number of visible rows. When it's 1, it means only the header is there and no records are returned.