If statement for autofilter?

JayLee

Board Regular
Joined
Sep 23, 2002
Messages
52
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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
 

Forum statistics

Threads
1,144,362
Messages
5,723,909
Members
422,527
Latest member
JayTheKaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top