Autofilter question.

[JayLee]

I have a function that in each worksheet I have it call function "Exchanges". This basically autofilters the worksheet for the word "exchanges" in column D. Then it would just copy all the found rows to a new worksheet called "exchange cancel". I tried to write code to do this. Unfortunately I wrote it on the assumption that every worksheet had atleast one found row. If there wasn't any found, macro would crash. Is there a way to tell autofilter to skip if there isn't any copied rows? Thanks in Advance!

 

[Geert]

Hi,

how about first checking the column and if the amount of "Exchange" is less then 1 then nothing, else autofilter?

Geert

 

[colinharwood]

Hi
i do a similar thing with a spreadsheet for accounts where i want to find any entries which are unpaid, which are in col with a blank entry. the following code searches for blank entries and then copies them to a new sheet. It works for me even if a month has no unpaid entries, give it a try

Application.ScreenUpdating = False
'Clear unpaid entries
Sheets("Unpaid").Select
Range("A5:I1000").Select
Selection.ClearContents
'New month April
Sheets("April").Select
Rows("27:27").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="="
Range("G26").Select
Selection.End(xlDown).Select
Selection.Offset(0, 2).Select
Range(Cells(ActiveCell.Row + 0, ActiveCell.Column), Cells(28, ActiveCell.Column - ).Select
Selection.Copy
'Add to unpaid list
Sheets("Unpaid").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("April").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A26").Select
'New Month May
Sheets("Unpaid").Select
Range("A3").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
'add text unpaid entries for " "
ActiveCell.FormulaR1C1 = "."
Selection.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "Unpaid Entries for May"
With Selection
.HorizontalAlignment = xlLeft
End With
With Selection.Font
.FontStyle = "Bold Italic"
.ColorIndex = 3
End With
'Select unpaid entries
Sheets("May").Select
Rows("27:27").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="="
Range("G26").Select
Selection.End(xlDown).Select
Selection.Offset(0, 2).Select
Range(Cells(ActiveCell.Row + 0, ActiveCell.Column), Cells(28, ActiveCell.Column - ).Select
Selection.Copy
Sheets("Unpaid").Select
Range("A3").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("May").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A26").Select
'New Month June
Sheets("Unpaid").Select
Range("A3").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
'add text unpaid entries for " "
ActiveCell.FormulaR1C1 = "."
Selection.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "Unpaid Entries for June"
With Selection
.HorizontalAlignment = xlLeft
End With
With Selection.Font
.FontStyle = "Bold Italic"
.ColorIndex = 3
End With
'Select unpaid entries
Sheets("June").Select
Rows("27:27").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="="
Range("G26").Select
Selection.End(xlDown).Select
Selection.Offset(0, 2).Select
Range(Cells(ActiveCell.Row + 0, ActiveCell.Column), Cells(28, ActiveCell.Column - ).Select
Selection.Copy
Sheets("Unpaid").Select
Range("A3").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("June").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A26").Select
'New Month July

There might be an easier way but this works for me

Colin

 

[JayLee]

Could I just use:

Range("A:A").SpecialCells(xlBlanks).Select

Would that select the next blank row?