PDA

View Full Version : Solved: Creating and Sorting a Dynamic Range



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!

mancubus
06-24-2013, 12:13 PM
do you really need all range variables for a simple sorting?

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

Sock
06-24-2013, 01:10 PM
do you really need all range variables for a simple sorting?

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


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
06-24-2013, 01:59 PM
second message was not posted because of "server is busy at the moment"...

here it is:

for error line:

change
Set CIndex = Range(CSt.End(xlDown))

to
Set CIndex = CSt.End(xlDown)

mancubus
06-24-2013, 02:05 PM
rows 40 and 41 contain headers?

if so:

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

Sock
06-24-2013, 02:08 PM
rows 40 and 41 contain headers?

if so:

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


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

mancubus
06-24-2013, 02:11 PM
Key indicates the column that contain the values to sort.
so Mx, x being any valid row number, makes it work.

mancubus
06-24-2013, 02:12 PM
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.