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*"

HansVogelaar

MVP

Jan 10, 2022

craygoza92

Perhaps you can use ActiveSheet.UsedRange instead.

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?