Link to home
Start Free TrialLog in
Avatar of mat58
mat58

asked on

Excel VBA sorting multiple columns with headers

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.
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mat58
mat58

ASKER

I still receive an error:  "Unable to get the sort property of the Range class"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mat58

ASKER

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 ?
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?
Why not remove the blanks with autofilter instead?
Avatar of mat58

ASKER

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
Avatar of mat58

ASKER

Forcing the select for the worksheet and the range resolved the problem.