How do I sort with merged cells?

[slam]

I have data in A1 through V17.

Row 1 is my header row.

A2:A3 are merged, A4:A5 are merged, and that trend continues up to and including A16:A17

Similarly:
V2:V3 are merged, V4:V5 are merged, and that trend continues up to and including V16:V17

I need to do a descending value sort on column V, but I always want row 2 and 3 to stay together, 4 and 5 and so on up to and including row 16 and 17.

How do I go about sorting this? Any help would be greatly appreciated. Thank you.

 

[shg]

The universal answer to the manifold problems of merged cells is to unmerge them.

 

[jamorin]

if you have the cells merged for appearance sake, could you use ALT-ENTER to create a line break within the cells in Column A and V. Then everything would be one row, but still have the appreance you are looking for.

 

[slam]

The universal answer to the manifold problems of merged cells is to unmerge them.

Don't think that's an option unfortunately....

if you have the cells merged for appearance sake, could you use ALT-ENTER to create a line break within the cells in Column A and V. Then everything would be one row, but still have the appreance you are looking for.

No, its not for appearance - how do I post the spreadsheet here like I see everyone doing?

 

[Rick Rothstein]

No, its not for appearance - how do I post the spreadsheet here like I see everyone doing?

You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[slam]

So here I think you can see why I need this:

 

[Rick Rothstein]

Merge cells always seem to cause problems. One thing... you can't sort with them. Here is a macro that you can run to unmerge the cells just before you sort them and then run again to remerge them....

Sub ToggleMergeForColumnsAandV()
  Dim X As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For X = 2 To LastRow Step 2
    If Cells(X, "A").MergeCells Then
      Cells(X, "A").MergeArea.UnMerge
      Cells(X + 1, "A").Value = Cells(X, "A").Value
      Cells(X, "V").MergeArea.UnMerge
      Cells(X + 1, "V").Value = Cells(X, "V").Value
    Else
      Application.DisplayAlerts = False
      Cells(X, "A").Resize(2).Merge
      Cells(X, "V").Resize(2).Merge
      Application.DisplayAlerts = True
    End If
  Next
  Application.ScreenUpdating = True
End Sub

One thing with your sort, though... I think you should sort on both Column V and Column A (Column V as primary, Column A as secondary), otherwise there might be a chance for identical values in the unmerged Column V to split the unmerged Teams apart from each other.

 

[slam]

Merge cells always seem to cause problems. One thing... you can't sort with them. Here is a macro that you can run to unmerge the cells just before you sort them and then run again to remerge them....

Sub ToggleMergeForColumnsAandV()
  Dim X As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For X = 2 To LastRow Step 2
    If Cells(X, "A").MergeCells Then
      Cells(X, "A").MergeArea.UnMerge
      Cells(X + 1, "A").Value = Cells(X, "A").Value
      Cells(X, "V").MergeArea.UnMerge
      Cells(X + 1, "V").Value = Cells(X, "V").Value
    Else
      Application.DisplayAlerts = False
      Cells(X, "A").Resize(2).Merge
      Cells(X, "V").Resize(2).Merge
      Application.DisplayAlerts = True
    End If
  Next
  Application.ScreenUpdating = True
End Sub

One thing with your sort, though... I think you should sort on both Column V and Column A (Column V as primary, Column A as secondary), otherwise there might be a chance for identical values in the unmerged Column V to split the unmerged Teams apart from each other.

I get what that is doing - seems great. Could it go a step further, and run upon entering the sheet to unmerge, then sort, then run again to remerge?

Thanks

 

[Rick Rothstein]

Okay, here is the macro restructured to do the unmerge, sort and then merge again (install it in a standard module like you would do for any other macro)...

Sub UnmergeSortMerge()
  Dim X As Long, LastRow As Long
  Application.ScreenUpdating = False
  ' Unmerge cells
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow Step 2
    Cells(X, "A").MergeArea.UnMerge
    Cells(X + 1, "A").Value = Cells(X, "A").Value
    Cells(X, "V").MergeArea.UnMerge
    Cells(X + 1, "V").Value = Cells(X, "V").Value
  Next
  ' Sort
  Range("A:V").Sort Key1:=Range("V1"), Order1:=xlAscending, _
                    Key2:=Range("A1"), Order2:=xlAscending, _
                    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
                    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
                    DataOption2:=xlSortNormal
  ' Merge cells
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow Step 2
    Application.DisplayAlerts = False
    Cells(X, "A").Resize(2).Merge
    Cells(X, "V").Resize(2).Merge
    Application.DisplayAlerts = True
  Next
  Application.ScreenUpdating = True
End Sub

As written, you still have to call this from somewhere; meaning, via the ALT+F8 macro dialog box, or you can assign it to a button and use that to call it up, or you can call it from other VB code. You indicated that you wanted this to run automatically "upon entering the sheet". To do that, call up the worksheet's code module and put this in it...

Private Sub Worksheet_Activate()
UnmergeSortMerge
End Sub

 

[slam]

Okay, here is the macro restructured to do the unmerge, sort and then merge again (install it in a standard module like you would do for any other macro)...

Sub UnmergeSortMerge()
  Dim X As Long, LastRow As Long
  Application.ScreenUpdating = False
  ' Unmerge cells
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow Step 2
    Cells(X, "A").MergeArea.UnMerge
    Cells(X + 1, "A").Value = Cells(X, "A").Value
    Cells(X, "V").MergeArea.UnMerge
    Cells(X + 1, "V").Value = Cells(X, "V").Value
  Next
  ' Sort
  Range("A:V").Sort Key1:=Range("V1"), Order1:=xlAscending, _
                    Key2:=Range("A1"), Order2:=xlAscending, _
                    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
                    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
                    DataOption2:=xlSortNormal
  ' Merge cells
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 2 To LastRow Step 2
    Application.DisplayAlerts = False
    Cells(X, "A").Resize(2).Merge
    Cells(X, "V").Resize(2).Merge
    Application.DisplayAlerts = True
  Next
  Application.ScreenUpdating = True
End Sub

As written, you still have to call this from somewhere; meaning, via the ALT+F8 macro dialog box, or you can assign it to a button and use that to call it up, or you can call it from other VB code. You indicated that you wanted this to run automatically "upon entering the sheet". To do that, call up the worksheet's code module and put this in it...

Private Sub Worksheet_Activate()
UnmergeSortMerge
End Sub

I'm quite proud of myself - I almost had that!

Couple of tweaks - made the first sort descending, and just made this macro a worksheet_activate() one, rather than making a separate one that calls it.

Works great. Thanks so much for your help!