Need to select all data in table, then filter the data

[kwp004]

I'm building a macro that filters a table. Rather than use Range A1:R1099, I want to select all of the data in the worksheet.


Range("A1:R1099").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"A2:A1099"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"R2:R1099"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"I2:I1099"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"E2:E1099"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"P1,Referral,C1", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"F2:F1099"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"N1,E1,W1,C1,C2" _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:R1099")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[kwp004]

Basically, rather than go to 1099 for the last row, I need to go to whatever the last row in the table is.

 

[Fluff]

How about

   With ActiveWorkbook.Worksheets("Sheet1").Sort
      .SortFields.Clear
      .SortFields.Add2 Range("A1"), xlSortOnValues, xlAscending, , xlSortNormal
      .SortFields.Add2 Range("R1"), xlSortOnValues, xlAscending, , xlSortNormal
      .SortFields.Add2 Range("I1"), xlSortOnValues, xlAscending, , xlSortNormal
      .SortFields.Add2 Range("E1"), xlSortOnValues, xlAscending, "P1,Referral,C1", xlSortNormal
      .SortFields.Add2 Range("F1"), xlSortOnValues, xlAscending, "N1,E1,W1,C1,C2", xlSortNormal
      .SetRange Range("A1").CurrentRegion
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With