asked on

How do I get an Excel 2007 sort macro to recognize variable columns and rows?

I recorded this macro. It fails to recognize variability when adding/deleting rows and columns. Although I have made this sheet into a table, it still does not recognize the variablity. Any suggestions on building a sort macro to account for a sheet or table sort with row and column variablity is appreciated. Attached is the spreadsheet. The macro is "Test".

ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
SortFields.Add Key:=ActiveCell.Offset(0, -21).Range("A1:A16"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Test.xlsm

Steve

Roughly...

lLastRow = ActiveWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    ActiveWorkbook.Worksheets("Sheet1").ListObjects("Sheet1").Sort. _
         SortFields.Clear
     ActiveWorkbook.Worksheets("Sheet1").ListObjects("Sheet1").Sort. _
         SortFields.Add Key:=ActiveCell.Offset(0, -21).Range("A1:A" & lLastRow), SortOn:= _
         xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Sheet1").Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With

Open in new window

Steve

Renamed for your sheet...

lLastRow = ActiveWorkbook.Worksheets("Attendance").Cells(Rows.Count, 1).End(xlUp).Row
    ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
         SortFields.Clear
     ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
         SortFields.Add Key:=ActiveCell.Offset(0, -21).Range("A1:A" & lLastRow), SortOn:= _
         xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
     With ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With

Open in new window

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

William C Johnson

ASKER

Glenn, your solution worked the best. Thank you for your help. I can now duplicate this functionality.