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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

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