Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Excel 2000 - SP3

    I have a (long) table which I need to sort, by column A.

    Each row contains merged cells, including the cells in column A.

    When I try to sort, I get the message "This operation requires the merged cells to be identically sized"

    I am attaching a sample of (4 rows of) my table.

    I appreciated that the conventional sort in Excel will not work, but just wondered if anyone can suggest a workaround to solve this sorting problem?

    Many thanks

    Neil
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's why you should never merge cells in Excel if you want to manipulate the cells in any way (filter, sort, ...)

    I think you'll have to unmerge all merged cells...

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Hans

    If you don't know of one, I will assume there is no clever workaround!

    Cheers

    Neil

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Hang in there - we have thousands of new Loungers, perhaps someone will come up with a suggestion.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Are any rows grouped differently than those in your sample?
    Are there any header rows? If so, how many?
    Regards
    Don

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Don

    There are 5 header rows, and all "rows" below the header are in the same format as the sample.

    Thanks

    Neil

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by neil View Post
    Don

    There are 5 header rows, and all "rows" below the header are in the same format as the sample.

    Thanks

    Neil
    Although the following code should work, it in no way detracts from Hans' admonition to avoid merged cells. They will kill you.

    Be patient; this will take some time.
    Code:
    Dim LastRow As Long
    Const FirstRow = 6
    Const Merged = 4
    Const FirstCol = 1
    Const LastCol = 10
    Const FirstSplit = 3
    Const LastSplit = 5
    Dim TempCell As Variant
    Dim MainCtr As Long
    Dim SplitCtr As Long
    Dim ColCtr As Long
    
    Option Explicit
    
    Public Sub SpecialSort()
    	LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    	
    	For MainCtr = FirstRow To LastRow Step Merged
     	If MainCtr = LastRow Then Exit Sub
     	If Cells(MainCtr, 1) > Cells(MainCtr + Merged, 1) Then
     	For ColCtr = FirstCol To LastCol
     	If ColCtr >= FirstSplit And ColCtr <= LastSplit Then
     	Call MoveSplit
     	Else
     	Call MoveMerged
     	End If
     	Next ColCtr
     	MainCtr = FirstRow - Merged
     	End If
    	Next MainCtr
    End Sub
    
    Public Sub MoveMerged(Optional Abort As Boolean)
    	TempCell = Cells(MainCtr, ColCtr)
    	Cells(MainCtr, ColCtr) = Cells(MainCtr + Merged, ColCtr)
    	Cells(MainCtr + Merged, ColCtr) = TempCell
    End Sub
    
    Public Sub MoveSplit(Optional Abort As Boolean)
    	
    	For SplitCtr = 0 To Merged - 1
     	TempCell = Cells(MainCtr + SplitCtr, ColCtr)
     	Cells(MainCtr + SplitCtr, ColCtr) = Cells(MainCtr + Merged + SplitCtr, ColCtr)
     	Cells(MainCtr + Merged + SplitCtr, ColCtr) = TempCell
    	Next SplitCtr
    End Sub
    Regards
    Don

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Don

    I'm stunned!

    I knew there must be a way, and I have just tried your code, and (as you rightly predicted) it works!

    A thousand thanks.

    And I promise never to use merged cells again...

    Cheers

    Neil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •