Solved: Creating and Sorting a Dynamic Range

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!

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

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

Quote:

---

Originally Posted by 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]

---

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!

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]

rows 40 and 41 contain headers?

if so:

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

Quote:

---

Originally Posted by mancubus

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)

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

Quote:

---

Originally Posted by Sock

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.