Forum Discussion
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").AutoFilter Field:=3, Criteria1:="*PF*"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "PF"
ActiveSheet.Paste
Sheets("All Shipments").Select
Selection.ClearContents
Sheets("PF").Select
Range("A1:W1").Select
Selection.Copy
Sheets("All Shipments").Select
Range("A1").Select
ActiveSheet.Paste
Range("A6:V99999").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Please note the "99999s" this is the only way i could come with to select the maximum active range but since this code repeats above ten times, it ends up taking a bit of time. Is there any way to make excel count the number of rows and set the range based on that number? that way it doesn't have to make calculations up to row 99999.
Thanks for your help!.
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*"
5 Replies
Perhaps you can use ActiveSheet.UsedRange instead.
- 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.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*"
- which part of my actual code would i replace that in ? thanks.
