?
Solved

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

Posted on 2014-10-17
4
Medium Priority
?
194 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
[X]
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
  • 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 2000 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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 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