Solved: Creating and Sorting a Dynamic Range

[Sock]

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 )

[VBA]
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
[/VBA]

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!

[mancubus]

do you really need all range variables for a simple sorting?

[VBA]Range("D40").CurrentRegion.Sort Key1:=Range("M40"), Order1:=xlAscending, Header:=xlGuess
[/VBA]

[Sock]

do you really need all range variables for a simple sorting?

[VBA]Range("D40").CurrentRegion.Sort Key1:=Range("M40"), Order1:=xlAscending, Header:=xlGuess
[/VBA]

I'm hoping I don't have to use all those range variables hahahaha

I didn't know about the 'CurrentRegion' property. However, I've tried to sue yours, but I get a "Sort method of range class failed" error.* I'm also a little uncertain how your "M40" key would grab the data downwards to the end of the column for sorting.

*EDIT: I have a sort working now using 'CurrentRegion', with no errors. However, it grabs headers that are directly above and sorts through them (there are two rows of headers). Is there a way to keep it from looking upwards and pulling from above? I've circumvented the problem momentarily by placing the headers after sorting.

Thank you so much!

[mancubus]

second message was not posted because of "server is busy at the moment"...

here it is:

for error line:

change
[VBA]Set CIndex = Range(CSt.End(xlDown))[/VBA]

to
[VBA]Set CIndex = CSt.End(xlDown)[/VBA]

[mancubus]

rows 40 and 41 contain headers?

if so:

[VBA]Range("D40").CurrentRegion.Offset(2).Sort Key1:=Range("M40"), Order1:=xlAscending, Header:=xlNo
[/VBA]

[Sock]

rows 40 and 41 contain headers?

if so:

[VBA]Range("D40").CurrentRegion.Offset(2).Sort Key1:=Range("M40"), Order1:=xlAscending, Header:=xlNo
[/VBA]

Rows 38 and 39 contain headers (row 39 has some blank spaces, however, including in the "M" column)

[mancubus]

Key indicates the column that contain the values to sort.
so Mx, x being any valid row number, makes it work.

[mancubus]

Rows 38 and 39 contain headers (row 39 has some blank spaces, however, including in the "M" column)

change D40 to D38 in post#5.