Solved

Excel VBA sorting multiple columns with headers

Posted on 2011-03-25
8
4,262 Views
Last Modified: 2012-05-11
Hello,

I am struggling with sorting a range in Excel using VBA.  I have the following code:

dim wp as worksheet
set wp = worksheets("Parameters")
lastrow = wp.Range("Z" & Rows.Count).End(xlUp).Row

With wp
    .Sort.SortFields.Add Key:=Range( _
        "AA2:AA" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range( _
        "Z2:Z" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("Z2:AC" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Every time I try to sort I receive an error:
Run-time error '1004':
"The sort reference is not valid.  Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"    

I'm sure this is just a simple code issue, but I'm stumped.  Can someone please help me ?  This code is just part of a much bigger program.
0
Comment
Question by:mat58
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 150 total points
Comment Utility
try using  this code
Do you have data all the way through column AC?

Thomas

dim wp as worksheet
set wp = worksheets("Parameters")
lastrow = wp.Range("Z" & Rows.Count).End(xlUp).Row

With wp
    .Range("Z2:AC" & lastrow).Sort.SortFields.Add Key:=Range( _
        "AA2:AA" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    .Range("Z2:AC" & lastrow).Sort.SortFields.Add Key:=Range( _
        "Z2:Z" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("Z2:AC" & lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Open in new window

0
 

Author Comment

by:mat58
Comment Utility
I still receive an error:  "Unable to get the sort property of the Range class"
0
 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 100 total points
Comment Utility
Your code runs fine for me on 2007 & 2010.   Take a look at the Sort dialog from the Data ribbon and/or the data in your range for any clues to the error message

"...Make sure that it's within the data you want to sort, and the first sort by box isn't the same or blank"
0
 

Author Comment

by:mat58
Comment Utility
The data range does have blank rows amongst it.   My overall goal is to sort is to compress the data and have the blanks at the bottom.   The first sort box (header) is not blank.  The blanks in the middle, is that causing the problem ?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 6

Expert Comment

by:reitzen
Comment Utility
I don't think the empty cells are the problem, and shouldn't be, by design.  I tried it with an empty cell in the first row of columns AA & Z, as well as with empty cells at the end and middle of the range.  Each time it sorted fine.

Could the values in the column headers (row 2) be words that might force this error?
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Why not remove the blanks with autofilter instead?
0
 

Author Comment

by:mat58
Comment Utility
I figured it out.  Because there is a number of sheet changes throughout the program, I just added the following prior to the sort:

Sheets("Paramaters").select
Range("Z:AC").select

Thanks for your help
0
 

Author Closing Comment

by:mat58
Comment Utility
Forcing the select for the worksheet and the range resolved the problem.
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

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
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 Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

11 Experts available now in Live!

Get 1:1 Help Now