Selecting and sorting variable range

[radka.silva]

Hello,

I am kind of new to this and very basic knowledge of VBA, so I apologize if my questions are trivial. I generally just record my macros and then I can do some basic edits, but I am kind of stuck with this one.

I am trying to select a range of cells and sort it. The selection will always start in cell A4 and end in column G; the number or rows will vary. I don't want to put a set range of A4:G10000 (some large number) because I have a summary row, that I am trying to exclude from the sort. So my last row needs to be the one before where A doesn't have any value.

Regarding the sort: this one I just recorded the macro and wanted to use that, but it is referencing the name of the Worksheet and my Worksheet names will always be different, so I just want it to work on the current active sheet.

This is the code for the moment:

Range("A4:G44").Select
ActiveWorkbook.Worksheets("DOWNLOAD7").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DOWNLOAD7").Sort.SortFields.Add Key:=Range( _
"B4:B44"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DOWNLOAD7").Sort
.SetRange Range("A4:G44")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

I am using Excel 2007.

Thank you,

Radka

[Simon Lloyd]

Try:
Range("A4:G" & range("A" & rows.count).end(xlup).row).Select
Activesheet.Sort.SortFields.Clear
Activesheet.Sort.SortFields.Add Key:=Range( _
"B4:B44"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With Activesheet.Sort
.SetRange Range("A4:G" & range("A" & rows.count).end(xlup).row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

[radka.silva]

Perfect! Thank you so much!!!

[radka.silva]

Sorry, one more question: 4th line is still referencing a set number of rows:

ActiveSheet.Sort.SortFields.Add Key:=Range( _
"B4:B44"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

Isn't that going to be a problem if I have more or less rows?

[JKwan]

you can apply Simon's principle / extrapolate / by example:


lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A4:G" & lastrow).Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"B4:B" & lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A4:G" & lastrow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With