Sock
06-23-2013, 12:09 PM
For the record, I did check the recent post by "Etheer" about sorting a range. With that said, let me begin.
I have a range of cells that will always begin in D40 to M40 and down a set of rows. However, the range may be adjusted downwards based on previous scripts. ie. it could go from D40:M60 or just D40:M45, etc. This the main thing I'm struggling with defining. Likewise, I want to sort the data from maximum to minimum, based on the data in column M40:wherever.
I have made a ham-handed attempt (please don't laugh :( )
Sub SortingThing()
Dim BuildRange As Range, SortRange As Range, CSt As Range, CIndex As Range
Set BuildRange = Range("D40")
Set SortRange = Range(BuildRange.End(xlToRight), BuildRange.End(xlDown))
Set CSt = Range("M40")
Set CIndex = Range(CSt.End(xlDown)) '*** I receive an error here
Worksheets("SumReport").Sort.SortFields.Clear
Worksheets("SumReport").Sort.SortFields.Add Key:=Range( _
CIndex), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Worksheets("SumReport").Sort
.SetRange Range(SortRange)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
I'm willing to bet there's a better way to do this. I'm open to basically anything.
Thank you so much in advance!
I have a range of cells that will always begin in D40 to M40 and down a set of rows. However, the range may be adjusted downwards based on previous scripts. ie. it could go from D40:M60 or just D40:M45, etc. This the main thing I'm struggling with defining. Likewise, I want to sort the data from maximum to minimum, based on the data in column M40:wherever.
I have made a ham-handed attempt (please don't laugh :( )
Sub SortingThing()
Dim BuildRange As Range, SortRange As Range, CSt As Range, CIndex As Range
Set BuildRange = Range("D40")
Set SortRange = Range(BuildRange.End(xlToRight), BuildRange.End(xlDown))
Set CSt = Range("M40")
Set CIndex = Range(CSt.End(xlDown)) '*** I receive an error here
Worksheets("SumReport").Sort.SortFields.Clear
Worksheets("SumReport").Sort.SortFields.Add Key:=Range( _
CIndex), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With Worksheets("SumReport").Sort
.SetRange Range(SortRange)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
I'm willing to bet there's a better way to do this. I'm open to basically anything.
Thank you so much in advance!