Advanced Filter Macro - Finding Last Row?

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115
I have the following code that works but the files will have different rows from week to week so I am trying to get the last rows for each range.

Columns("A:A").Select
Range("A1:A29807").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("old-Complete Daily Inventory File.xlsx").Sheets( _
"Complete Daily Inventory File").Range("A1:A29719"), Unique:=False

Do you guys have any idea of how I can accomplish this? I greatly appreciate your time.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
lastRow = Range("A" & Rows.Count).End(xlup).Row
Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
 

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115
Thanks for your response but I got an error when doing this. Below is the code that I put in:

Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("old-Complete Daily Inventory File.xlsx").Sheets( _
"Complete Daily Inventory File").Range("A1:A" & lastRow), Unique:=False
 

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115
Actually, I see that I didn't put the definition in. Now I have the following but it no longer filters anything:

lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("old-Complete Daily Inventory File.xlsx").Sheets( _
"Complete Daily Inventory File").Range("A1:A" & lastRow), Unique:=False

Any ideas?
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609

ADVERTISEMENT

No idea without looking at your dataset. Try running the code line by line to ensure that lastRow is equal to the last row in your dataset. To do this, simple press F8 until lastRow = Range("A" & Rows.Count).End(xlUp).Row is highlighted yellow. Then press F8 1 more time. Then put your cursur on the part that says lastRow. A pop up will show you the value of lastRow and it should equal the last row of your dataset. If it does equal the last row of your dataset, then there is nothing wrong with the code I gave you. If your code worked before I gave you the new code to modify, then it should work now.
 

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115
Thanks again for your help. It looks like it is giving the last row for the old file and using it in both the list range and the criteria range. Below is the code that I have now and is still not filtering:

Application.DisplayAlerts = False
Columns("A:A").Select
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("old-Complete Daily Inventory File.xlsx").Sheets( _
"Complete Daily Inventory File").Range("A1:A" & lastRow), Unique:=False
Windows("old-Complete Daily Inventory File.xlsx").Activate
Windows("Complete Daily Inventory File.xlsx").Activate
 

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115

ADVERTISEMENT

It seems that it is giving the row for the workbook that is open and active so it is giving the wrong number. Does anybody have an idea of how to define what workbook that it pulls the lasRow from?
 

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
Oh good you figured it out. Yes you can define the workbook and worksheet that lastRow should equal. I think it goes something like this...
lastRow = Application.Workbooks("workbookName").Sheets("sheetName").Range("A" & Rows.Count).End(xlUp).Row
 

Southerncentralrain

Board Regular
Joined
Jul 6, 2008
Messages
115
Yes, I used the following code at the top to define it and it worked. Thanks again for your time and help.

With Workbooks("old-Complete Daily Inventory File.xlsx").Sheets( _
"Complete Daily Inventory File")
CriteriaLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,062
Members
409,684
Latest member
Nazmul00

This Week's Hot Topics

Top