Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-17
4
189 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel TimeLine stopped working? 36 60
Turn date into age 17 32
V-Lookup 11 17
New Email window show up in front of any other window 3 12
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

839 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