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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
Could the values in the column headers (row 2) be words that might force this error?
Why not remove the blanks with autofilter instead?
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").selec t
Range("Z:AC").select
Thanks for your help
Sheets("Paramaters").selec
Range("Z:AC").select
Thanks for your help
ASKER
Forcing the select for the worksheet and the range resolved the problem.
ASKER