Advanced Filter Macro - Finding Last Row?

[Southerncentralrain]

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.

 

[WarPigl3t]

lastRow = Range("A" & Rows.Count).End(xlup).Row
Range("A1:A" & lastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

 

[Southerncentralrain]

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]

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]

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]

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]

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]

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]

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