If statement for autofilter?

[JayLee]

I'm trying to construct an IF statement on an autofilter.

Sub ExchangeCancels()
'Process Cancels
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="=*exchange cancelled*", _
Operator:=xlAnd
'This doesn't seem to work If Selection.AutoFilter.Rows > 1 Then
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Exchange Cancels").Select
Range("A65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
'.Range("A1").
ActiveSheet.Paste
End If
End Sub

I want to make an if statment telling to only do those actions if the autofilter returns a row of data (excluding the column titles). The is run on multiple worksheets. How would I go about doing this? Thanks!
This message was edited by JayLee on 2002-10-05 00:15

 

[Tom Urtis]

Instead of using an If structure to determine the existence of your criteria, I'd suggest just letting the filter itself be the determining factor, with an error trap message box to tell you when no such criteria value exists in the filter range.

Modify the following revision of your original macro for last column (I assumed D), and for the sheet name in which the original data list resides (I assumed Sheet1).

I did a lot of declaring, and releasing the memory of, range variables, just 'cause.

Sub ExchangeCancels()
On Error GoTo e
Application.ScreenUpdating = False
Dim fRange As Range, cRange As Range, pRange As Range
Set fRange = Range(("A1"), Range("D65536").End(xlUp))
Set cRange = Range(("A2"), Range("D65536").End(xlUp))
Set pRange = Worksheets("Exchange Cancels").Range("A65536").End(xlUp).Offset(1, 0)
Worksheets("Sheet1").AutoFilterMode = False
fRange.AutoFilter Field:=4, Criteria1:="=*exchange cancelled*"
cRange.SpecialCells(xlCellTypeVisible).Copy pRange
Worksheets("Sheet1").AutoFilterMode = False
Set fRange = Nothing
Set cRange = Nothing
Set pRange = Nothing
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "No ''exchange cancelled'' data in column D.", 64, "Nothing to copy."
Worksheets("Sheet1").AutoFilterMode = False
End Sub

 

[Tom Urtis]

Just saw you are trying this on different sheets, not just one sheet. Here's an adaption that might work:

Sub ExchangeCancels()
On Error GoTo e
Application.ScreenUpdating = False
Dim fRange As Range, cRange As Range, pRange As Range
Set fRange = Range(("A1"), Range("D65536").End(xlUp))
Set cRange = Range(("A2"), Range("D65536").End(xlUp))
Set pRange = Worksheets("Exchange Cancels").Range("A65536").End(xlUp).Offset(1, 0)
fRange.AutoFilter Field:=4, Criteria1:="=*exchange cancelled*"
cRange.SpecialCells(xlCellTypeVisible).Copy pRange
Selection.AutoFilter
Set fRange = Nothing
Set cRange = Nothing
Set pRange = Nothing
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "No ''exchange cancelled'' data in column D.", 64, "Nothing to copy."
Range("A1").Select
Selection.AutoFilter
End Sub

 

[JayLee]

Yep, works perfectly! Thanks for all your help!