Autofilter question.

JayLee

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Geert

New Member
Joined
Jun 5, 2002
Messages
17
Hi,

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

Geert
 

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
397
Office Version
  1. 2019
Platform
  1. Windows
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 - :cool:).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 - :cool:).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 - :cool:).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

Board Regular
Joined
Sep 23, 2002
Messages
52
Could I just use:

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

Would that select the next blank row?
 

Forum statistics

Threads
1,144,329
Messages
5,723,736
Members
422,512
Latest member
MHau5

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