Solved

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

Posted on 2014-10-17
4
183 Views
Last Modified: 2014-10-19
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
0
Comment
Question by:cowboywm
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Steve
ID: 40387537
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

0
 
LVL 7

Expert Comment

by:Steve
ID: 40387540
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

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40387542
Since you've defined this as an Excel Table, you can continue using structured referencing.  Replace the second command above with the one here (in lines 4-6):
Sub Test()
    ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort. _
        SortFields.Add Key:=Range("Attendance[Role]"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Attendance").ListObjects("Attendance").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window


This guarantees that the sort will be dynamic - that is, follow the current defined range of the table "Attendance".
-Glenn
0
 

Author Closing Comment

by:cowboywm
ID: 40391234
Glenn, your solution worked the best.  Thank you for your help.  I can now duplicate this functionality.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now