radka.silva
02-18-2015, 10:37 AM
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
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