Dynamic CriteriaRange for Dynamic Filtering Macro

[Garmerr]

Hello All,
I am trying to enhance a macro to allow for a dynamic criteria range that will be used for creating a dynamic filter. In the code below, the CriteriaRange is currently defined as:

CriteriaRange:=Range("Q3:Q11")

What I want is for the list to Q3:Q#, where number represents the last row with data in this column. This list will change in the number of rows populated, but will always start at Q3.

Sub ItemList()'
' ItemList Macro
' Copy Unique Item List
'


'
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("Table1[[#All],[Supplier]:[Description]]").AdvancedFilter Action:= _
        xlFilterCopy, [B]CriteriaRange:=Range("Q3:Q11")[/B], CopyToRange:=Range("W2"), _
        Unique:=True
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range


Set sht = Worksheets("Current Quarter FC")
Set StartCell = Range("W3")


'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column


'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
    Selection.Copy
    Sheets("SC Item Review").Select
    Range("A2").Select
    ActiveSheet.Paste
    Columns("A:D").Select
    Selection.Columns.AutoFit
    Range("A2").Select
Dim LastR As Long


'FIND LAST ROW OF DATA IN COLUMN 1 (A)
LastR = Cells(Rows.Count, 1).End(xlUp).Row


'COPY FROM ROW 2 OF COL 5 (E) TO LAST ROW OF COLUMN A IN COLUMN 8 (H)
Range(Cells(2, 5), Cells(LastR, 10)).FillDown
    Range("H2").Select
End Sub

Thanks for your time.

 

[Fluff]

How about

[B]Range("Q3",range("Q"&Rows.Count).end(xlup))[/B]

 

[Garmerr]

Excellent. Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback