Sorting Using VBA

rglasunow · #1 09-04-2007, 06:47 PM

I am trying to sort three different columns using VBA. I am running into several issues. Below is the code I currently have.

Range("Pending").sort Key1:=Range("V5"), Order1:=xlDescending, Key2:=Range("W5") _
, Order2:=xlDescending, Key3:=Range("X5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("V5").Select
Selection.End(xlDown).Select

Here’s what I’m trying to achieve.

• Highlight the entire row from row 5 down to the very last data cell in column A.
• Sort in order by column V, W then X.

I’m having trouble selecting the entire row and having the selection stop at the last cell in column A.

Any help is greatly appreciated.

Thanks.

FrankBoston · #2 09-04-2007, 07:00 PM

Maybe this will do it:

Code:

Range("Pending").EntireRow.Sort _
     Key1:=Range("V5"), Order1:=xlDescending, DataOption1:=xlSortNormal, _
     Key2:=Range("W5"), Order2:=xlDescending, DataOption2:=xlSortNormal, _
     Key3:=Range("X5"), Order3:=xlDescending, DataOption3:=xlSortNormal, _
     Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom_

Range("V5").End(xlDown).Select

rglasunow · #3 09-06-2007, 02:26 PM

Thanks for your help Frank but unfortunately this didn’t work. Does anyone have any other suggestions to what I’m trying to achieve (see above).

Thank you!!

Ikaabod · #4 09-06-2007, 03:03 PM

I'm assuming your sort is already set up properly and that you just need the code for the proper selection. In which case, this code should work:

Code:

iLastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A5:A" & iLastRow).EntireRow.Select
Selection.Sort Key1:=Range("V5"), Order1:=xlDescending, Key2:=Range("W5") _
, Order2:=xlDescending, Key3:=Range("X5"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

Welcome to the Excel Forum

Forum Statistics