Sorting Merged Cells

I'm trying to create a code that will sort a page with merged cells. I've attached an example.

In my example I have a list of names with dates. When you press the sort button the rows will sort by Column A. I add a lot of names and as my list grows I need to keep it organized so the sort function is vital.

On Sheet 1 you can see how the code should work. But when the list of dates gets too long I want to go to the row below and continue the dates. In these cases I want the name to merge. On Sheet two you can see how I'd like it to look with the name "Johnson, Paul". But since I have a merged cell, the sort function won't work.

Anybody have any ideas on how to pull this off? I was thinking that it could work if Excel could somehow unmerge the merged cells, add the same name to both cells, then do the sort, then merge any cells that match in Column A. But I don't know if this id doable.

Any help is appreciated.

6StringJazzer, I think you've helped me before. So I'll trust your judgement. Its not the answer I was hoping for, but I guess I'll have to consider this option. Thank you.

According to Sheet2 attachment as it is :

PHP Code: 

Sub SortDumbSheet()
        Dim Ra As Range, oCol As New Collection, V
   With ActiveSheet.UsedRange
     If Application.CountBlank(.Columns(1)) Then
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
       .Columns(1).UnMerge
        For Each Ra In .Columns(1).SpecialCells(4).Areas:  oCol.Add Array(Ra(0).Value2, Ra.Count + 1):  Ra = Ra(0):  Next
       .Sort .Cells(1), 1, Header:=1
        For Each V In oCol:  .Cells(Application.Match(V(0), .Columns(1), 0), 1).Resize(V(1)).Merge:  Next
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
     Else
        .Sort .Cells(1), 1, Header:=1
     End If
   End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

@Mark L - My real document is very very long. ATM I'm at over 2,000 rows. Thanks anyways though.

@6StringJazzer - I can definitely add more columns. I just preferred the aesthetic appearance of keeping everything within view without having to scroll horizontally. Its easier, just not as pretty as I wanted it in my mind.

Thank you both.

Originally Posted by dareeldill

@Mark L - My real document is very very long.

Anyway try my not too slow post #7 VBA procedure (as it depends on the number of merged areas) …

Rather than merging cells just use Center Across Coluimns if really necessary to 'merge' cell horizontally …

Anyway according to your Sheet2 attachment : how may rows in real ?
'Cause I have some ways but slower that the Excel sort feature, the more rows, the slower …

Mark,

I tried yours and it didn't work exactly as needed. I tried it with two people having the same name. In one situation it merged the cell below the matching names but not the matching names themselves.

I think I'll be ok with more columns though for now.

Thank you.

As on my side it well works with your initial post as it is, I can't reproduce any issue.

For not smart worksheet try with CurrentRegion instead of UsedRange …