Forum Discussion

Copper Contributor

Jan 10, 2022

Solved

Range 99999?

Hello & good morning. I've got the following code which sorts a file of shipments that vary in size every day. Currently I'm using the following code: ActiveSheet.Range("$A$1:W999999").A...

developer

excel

Formulas and Functions

Macros and VBA

alannavarro
Jan 10, 2022
craygoza92
Hello!

Hope it works, instead of this line of code
ActiveSheet.Range("$A$1:W999999").AutoFilter Field:=3, Criteria1:="*PF*"

Try this one
ActiveSheet.Range("A1", "W" & Cells(Rows.Count, 1).End(xlDown).Row).AutoFilter Field:=3, Criteria1:="*PF*"

craygoza92

Copper Contributor

Jan 10, 2022

i tried the following :

Sub macro1()

ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:="*T*"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "T"
ActiveSheet.Paste
Sheets("All Shipments").Select
Selection.ClearContents
Sheets("T").Select
Range("A1:W1").Select
Selection.Copy
Sheets("All Shipments").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.UsedRange.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

End Sub

it gives back the following error:
Run-time error '1004':
Application defined or object defined error.

alannavarro

Iron Contributor

Jan 10, 2022

craygoza92

Hello!

Hope it works, instead of this line of code

ActiveSheet.Range("$A$1:W999999").AutoFilter Field:=3, Criteria1:="*PF*"

Try this one

ActiveSheet.Range("A1", "W" & Cells(Rows.Count, 1).End(xlDown).Row).AutoFilter Field:=3, Criteria1:="*PF*"

craygoza92
Copper Contributor
Jan 10, 2022
Thanks Alan, it seems to have worked. I replaced two lines on the code:

Sub macro1()
ActiveSheet.Range("A1", "W" & Cells(Rows.Count, 1).End(xlDown).Row).AutoFilter Field:=3, Criteria1:=Array( _
"105701", "106177", "106182", "106583", "106709", "106722", "106909", "106991", _
"107025", "107111", "107222", "107500", "107777", "108888", "10677", "106777"), Operator:= _
xlFilterValues
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "INTERNETS"
ActiveSheet.Paste
Sheets("All Shipments").Select
Selection.ClearContents
Sheets("INTERNETS").Select
Range("A1:W1").Select
Selection.Copy
Sheets("All Shipments").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1", "W" & Cells(Rows.Count, 1).End(xlDown).Row).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

So the "& Cells(Rows.Count, 1).End(xlDown).Row)" is telling excel to grab that number and use it as the "W" range?