Sorting multiple ranges in a worksheet

Question

I'll have a worksheet that has data separated by merged cells.  There are four sets of data, and they're all the same number of columns, but the amount of rows in each set can vary.  I've tried finding the end of each set of data and using .Sort.Sortfields.Add to find the right range, but it's not working.

If I record a macro to sort a selection I get something like

ActiveWorkbook.Worksheets.Sort.Sortfields.Add Key:=Range(<selection>)

When I try to use that same code and just replace key with a Range such as:

Range("O35", Range("O35").Offset(placekeep, 0)) I get a compiling error on that line. 

So if anyone knows why Key:= doesn't like that Range, or how to sort a variable length of selections with the same field, I'd really appreciate your input.

Thanks again.

Answer 1

Not clear if you want to sort the four data sets "independent" of each other or if data should be moved "between" the data sets.

Assuming that no data should be moved between the data sets and that all data sets should be sorted by the first column, try this macro:

Sub sort_4_ranges()

  Dim ws As Worksheet

  Dim first_cell As Range

  Dim next_cell As Range

  Set ws = ActiveWorkbook.Worksheets("Sheet6")  'change name

  columns_to_sort = 10 'change to suit your data

  Set first_cell = ws.Range("E10") 'the first cell of the first data set to sort

  sorted_datasets = 0

  While sorted_datasets < 4

     Set next_cell = first_cell

     While next_cell.MergeArea.Count = 1

        Set next_cell = next_cell.Offset(1, 0)

     Wend

     'sorting code

     ws.Sort.SortFields.Clear

     ws.Sort.SortFields.Add Key:=Range(first_cell.Address), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

     With ws.Sort

        .SetRange Range(first_cell.Address & ":" & next_cell.Offset(-1, columns_to_sort).Address)

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

     End With

     Set first_cell = next_cell.Offset(1, 0)   'change the 1 if the next data set it not immediately after the row with merged cells

     sorted_datasets = sorted_datasets + 1

  Wend

End Sub

If the data should be sorted by some other column than the first column, change

Key:=Range(first_cell.Address)

to

Key:=Range(first_cell.Offset(0,column_to_sort_by-1).Address)

Hope this helps / Lars-Åke

Answer 2

Yep.  That was it. 

Is there anyway I for it to work without merging the cells below it?  You don't have to waste too much time answering, I'm happy enough with it now.  But it'd be better if those cells at the bottom didn't have to be merged.

Thanks

Answer 3

Lars - Your code gives the following error:  "Application or Object defined Error" on the line, "Set next_cell = next_cell.Offset(1, 0)"  But looking at it

That is probably because you don't have a merged cell AFTER the fourth set of data.

Insert a merged row there and see what happens.

Hope this helps / Lars-Åke

Answer 4

Thanks for the replies.

Rick - Your range still gave me the same error so there must have been something wrong with another aspect of my code.

Lars - Your code gives the following error:  "Application or Object defined Error" on the line, "Set next_cell = next_cell.Offset(1, 0)"  But looking at it looks like it would do exactly what I asked for, so hopefully you or someone else can figure out why I get that error because I have no clue.  I'm an ok programmer, but have only been using VBA for a couple of weeks now.

All I changed in your code was the Key:= section.  Otherwise it's all copy and pasted.  In case it helps:

I want all of the data sorted in the same way in the same column, but I want it to stay under the merged cell it was already under.  It's a program for a contract signing report, so the managers like to have a merged cell titled "pipeline", and one for "won," "lost" etc.  The cells don't have to be merged, I just thought it would be easier to find them in my programming like that.  But as Rick has told me a couple times now and I'm finding out, Merged cells are a pain. 

Basically all I want is a macro that does what I could do manually by doing Custom sort four different times on four different ranges with the same column.  I want to have five of them since there are five fields I want sorted, and each macro will be attached to a button.  So for example, column B is one of those fields, so I might have the following for the B column (manager name):

John Smith

Tim Burton

Frank Conn

(merged cells from A to S with "won" in the title...again they don't have to be merged if you can sort the data correctly and not these cells)

Tim Burton

John Smith

Frank Conn

And I want it to look like

Frank Conn

John Smith

Tim Burton

(merged)

Frank Conn

John Smith

Tim Burton

I'm also sorting for dates (highest to lowest) and revenues (highest to lowest, As Currency).  But I can figure out how to apply it to all my needs if someone can give me one macro that sorts four different selections the same way.

Thanks again.

Answer 5

I don't know the actual answer to your question (merged fields can be so messy), I'll leave that to other more knowledgeable in such things, but I wanted to point out that this awkwardly constructed (in my opinion) range expression...

Range("O35", Range("O35").Offset(placekeep, 0)).Address

can be replaced by this neater one (again, in my opinion)...

Range("O35").Resize(placekeep + 1).Address

The +1 is because Resize includes the cell you are referencing in its count whereas Offset does not.