0

I am trying to pull unique names from a list of names in my code. This currently works:

Set myRng = Sheets("quoteData_1").Range("Q2:Q8314")
Set r = Sheets("quoteData_1").Range("Z2")
myRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=myRng, CopyToRange:=r, Unique:=True

Currently I can hard code the column of cells that I wish to filter through for this but I would like to to alter this code so that it works with any length of the column as I will never know the exact length that I need. Between research and some experince I thought this would work but it does not ('rowIdx' is found earlier as an integer value pertaining to be the value of the last row):

Set myRng = Sheets("quoteData_1").Range(Cells(2,17),Cells(rowIdx,17))
Set r = Sheets("quoteData_1").Range("Z2")
myRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=myRng, CopyToRange:=r, Unique:=True

The Error I encounter reads: Run-time error '1004' Application-defined or object-defined error

So how am I able to use range with a varying number of cells and avoid using the name of the cell as in R5:R75?

jrodUSC
  • 1
  • 1
  • `Set myRng = Sheets("quoteData_1").Range(Sheets("quoteData_1".Cells(2,17),Sheets("quoteData_1".Cells(rowIdx,17))` – BigBen Oct 20 '22 at 16:40
  • Or `With Sheets("quoteData_1")`, `Set myRng = .Range(.Cells(2, 17), .Cells(rowIdx, 17))`, `End With`. – BigBen Oct 20 '22 at 16:41

0 Answers0