Dynamic sorting

[Cardstang]

I'm looking for a way to sort a range of rows in which the range is never the same.

An example:

The below represents a list of data that I need sorted. There are actually 26 columns of data, but I didn't bother to list them and won't unless it's needed.

What I'm trying to do is to sort the data within the section it is in. So, between "Corporate" and "Central" I need that data sorted. And between "Central" and "South" I need that sorted. And so on.

The total number of rows can be anywhere between 50 and 400. There is never the same amount.

The sort order is Ascending and is by Column C, then Column D.

Any suggestions?

Thanks!

Code: Select all

Corporate
5686
5729
5669
5322
2706
2406
Central
5699
5032
4419
4945
3570
South
4974
4950
5380
5540
4591
1807
4329
5619
West
3641
3948
4720
4038
4971
5328
East
5314
5537
5067
4040
5074
5642
2758
2725
2724

[HansV]

Will the categories always be text strings, and will the data always be numbers?

[Cardstang]

The categories will always be a text string.

Columns C and D are States and Cities respectively. Those are the only two columns that I will need to sort by, but I'll need to sort the entire row.

I do not need to sort by the numbers.

Thank you.

[HansV]

Now I'm confused. In the first post, you wrote

What I'm trying to do is to sort the data within the section it is in. So, between "Corporate" and "Central" I need that data sorted. And between "Central" and "South" I need that sorted.

I took this to mean that you wanted the numbers between two text strings sorted. But now you write

I do not need to sort by the numbers.

Apparently I'm missing something. Could you try to explain again what you want to accomplish?

[Cardstang]

Sorry for the confusion. I see how that is confusing.

I used the word "data" generically to mean the information contained in the rows.

The numbers are just one of the things that will be sorted, but the sorting will be done by text strings in columns C and D.

I was trying to make things simple and I failed miserably.

[HansV]

I understand even less now. Could you try to explain as clearly and completely as you can what exactly you want to sort?

[Cardstang]

I've added an example file.

I need each "section" sorted. Alphabetically by column C, and then by column D.

Using the attached file as a reference, the rows 3 thru 15 need sorted. Then rows 17-49 need sorted. And then 51-95 need sorted. All while remaining separate from the other sections.

Sometimes there are many rows, and other times just a few.

I hope this helps clarify things and doesn't add to the confusion.

[HansV]

OK, so you DO want to sort by the numbers in column A!

Try this macro:

Code: Select all

Sub SortWithinCategories()
  Dim r As Long
  Dim s As Long
  Dim m As Long
  m = Range("A" & Rows.Count).End(xlUp).Row
  r = 2
  Do While r <= m
    s = r + 1
    Do While s <= m And IsNumeric(Range("A" & s))
      s = s + 1
    Loop
    Range(r & ":" & (s - 1)).Sort Key1:=Range("A" & r), Header:=xlYes
    r = s
  Loop
End Sub

Make a copy of your workbook before testing it!

[Cardstang]

No, I don't want to sort by the numbers in column A.

I want to sort by State (Column C) and then by City (Column D). What is in column A is irrelevant to the end result of the sorting.

The example I provided had the rows sorted by Column A, and I need them sorted differently as described above.

What you provided sorts by column A just fine. But that's not how I need it sorted. I'll mess around with what you've provided and I might be able to get something figured out.

I apologize that I've made this so confusing. I'm so familiar with what I'm doing that I find it hard to put it into easier terms.

Thanks for your help. I really do appreciate it.

[HansV]

OK, you did mention in the first post that you wanted to sort by columns C and D.

Try this version:

Code: Select all

Sub SortWithinCategories()
  Dim r As Long
  Dim s As Long
  Dim m As Long
  m = Range("A" & Rows.Count).End(xlUp).Row
  r = 2
  Do While r <= m
    s = r + 1
    Do While s <= m And IsNumeric(Range("A" & s))
      s = s + 1
    Loop
    Range(r & ":" & (s - 1)).Sort Key1:=Range("C" & r), _
      Key2:=Range("D" & r), Header:=xlYes
    r = s
  Loop
End Sub

[Cardstang]

That did it!

Thank you so much.