Excel Excel 2003 VBA Sorting Issue

[jasonk]

I'm using VBA script + command button to sort a spreadsheet. Everything works except for the fact that it puts the blank rows on top of the data. Ideally, the blank rows would go to the bottom.

If one of you talented VBA programmers could help me modify the following code to put the blank rows below the data, I would be extremely grateful! Thx so much in advance. -Jason

Private Sub cmdSort_Click()
On Error GoTo Error_Handler

ActiveSheet.Unprotect
Range("D9:Y175").Select
Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Key2:=Range("E9") _
, Order2:=xlAscending, Key3:=Range("J9"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

Exit_Procedure:
On Error Resume Next
Exit Sub

Error_Handler:
MsgBox "An error has occurred in this application." & Err & ", " & Error & vbCrLf & vbCrLf & _
"Please contact your technical support person and report the problem.", vbExclamation, "Error!"
Resume Exit_Procedure
End Sub

 

[alow]

I don't know if I can necessarily help you with the VBA, but as far as concept, I would say that if you have blank rows in 9 through 175, that is the problem. If you have blank rows at the bottom, like I would expect, you would need to change your Range Select to a dynamic range that selects the first row and then to the end of the contiguous range of data. The keyboard shortcut would be Ctrl + Shift + End to select from the current cell to the end of the range, if that helps.