Sorting an Excel Table that contains Merged Cells

Question

I have a fairly simple Excel file, mostly layout (it's a report I've written), but midway down the document (row 28), I have a table that contains merged cells.

i.e. A | B | D | E | F

is as follows:

A | BCD | E | F

the same is done on the three rows below it, which contain the data, as follows:

Cell B28:D28 is merged

Cell B29:D29 is merged

Cell B30:D30 is merged

Cell B31:D31 is merged

When I select range A28:F31 I cannot sort by any column, error as follows:

"this operation requires merged cells to be identically sized"

Microsoft's response is simply that I need to make sure my cells are merged.

http://support.microsoft.com/kb/813974

Any advice? Other than unmerging cells? I am aware that I can Center Across Selection for cells, but for the purposes of this report, I am required to use merged cells.

Answer 1

You can't just copy the merged cell and "Paste as value" into a single cell? More directly, WHY do you have to used merged cells on this report? I personally can't think of any report that I've ever made that just COULD NOT be redone in a few different ways.

If you can tell us, as much as possible, about the layout of the report (Fields, Datatypes) or just post a screenshot it would help alot.

Unless someone has something I've never seen before, Short of copying your entire table to an array in VBA and using a sorting algorithm, you're going to have to find a way around having those few cells merged.

Again, Give an example as a layout and we'll go from there.

Answer 2

here is my answer to sort a excel range containing non identical merged cells. Here as specified in your question, we need to sort 'A' and accordingly others col i.e B,C,D.. will get sorted. Here i have specified a range where the data exist in excel "SortRangeValue" and the basic concept is run bubble sort on 'A' and if we find values to be swapped just Swap the entire row(which include both merged and separate rows) in given range i am having one hidden row at last, that's why i am running my code till lastRow-3, here 3 represents 1 for hidden row, 1 for length-1 for bubble sort and 1 for 0 based indexing in excel.(1+1+1=3) and Here it takes some time to execute as no of rows increases

Private Sub Ascending_Click()
    Dim myRange As Range        'variable to hold the Named Range
    Dim rowCount As Long        'variable to hold the Number of Rows in myRange
    Dim colCount As Long        'variable to hold the Number of Columns in myRange
    Dim rowStartIndex As Long   'variable to hold the Starting Row index of myRange
    Dim colStartIndex As Long   'variable to hold the Starting Col index of myRange
    Dim iIndex As Long          'Variable used for iteration
    Dim jIndex As Long          'Variable used for iteration
    Dim current As Long         'used in bubble sort to hold the value of the current jIndex item
    Dim currentPlusOne As Long          'used in bubble sort to hold the value of the  jIndex+1 item
    Dim rowIndex As Long
    Application.ScreenUpdating = False  'dont update screen until we sort the range.
    Set myRange = Range("SortRangeValue")   'Get the range
    '
    'get the columns and rows from where the row start, since Range can start from any cell
    ' also the no. of columns and rows in rows
    rowStartIndex = myRange.Row
    colStartIndex = myRange.Column
    colCount = myRange.Columns.Count
    rowCount = myRange.Rows.Count
    Dim tempCal As Long
    tempCal = rowCount + rowStartIndex          ' get the row no of last range
    'here colStartIndex is the very first column which is to be sorted
    For iIndex = 0 To rowCount - 3 Step 1       ' Run a bubble sort loop
        For jIndex = 0 To rowCount - iIndex - 3 Step 1
            rowIndex = jIndex + rowStartIndex
            current = Cells(rowIndex, colStartIndex)      ' calculate the rowIndex
            currentPlusOne = Cells(rowIndex + 1, colStartIndex)    ' get current cell value, and next row cell value.
            If current > currentPlusOne Then        'campair the values
                ' if match found, select entire row of the values and shift it m down by copying it to some temp location here it is (3,16)
                'get entire row from firstCol(colStartIndex) to last column  and copy it temp location (colStartIndex+colCount-1)
                Range(Cells(rowIndex + 1, colStartIndex), Cells(rowIndex + 1, colStartIndex + colCount - 1)).Copy Destination:=Cells(3, 16)
                Range(Cells(rowIndex, colStartIndex), Cells(rowIndex, colStartIndex + colCount - 1)).Copy Destination:=Cells(rowIndex + 1, colStartIndex)
                Range(Cells(3, 16), Cells(3, 16 + colCount - 1)).Copy Destination:=Cells(rowIndex, colStartIndex)
                Range(Cells(3, 16), Cells(3, 16 + colCount - 1)).Value = ""
            End If
           Next jIndex  ' increment jINdex
        Next iIndex     'Increment iIndex
        Application.ScreenUpdating = True       'display result on screen
  End Sub

Answer 3

Here is another solution which reduces time to execute from 30 sec to jst less than 2 sec. the problem with previous code was it was swapping rows so many times. in this code i am making copy of 'A' column and sorting it first, and then making one temp range in which i am saving entire rows of values which are sorted (Column 'A' entries) and then replacing temporary sorted range into original range.

Private Sub QuickAscending_Click()
Dim myRange As Range        'variable to hold the Named Range
Dim rowCount As Long        'variable to hold the Number of Rows in myRange
Dim colCount As Long        'variable to hold the Number of Columns in myRange
Dim rowStartIndex As Long   'variable to hold the Starting Row index of myRange
Dim colStartIndex As Long   'variable to hold the Starting Col index of myRange
Dim iIndex As Long          'Variable used for iteration
Dim jIndex As Long          'Variable used for iteration
Dim current As Long         'used in bubble sort to hold the value of the current jIndex item
Dim currentPlusOne As Long          'used in bubble sort to hold the value of the  jIndex+1 item
Dim rowIndex As Long
Dim tempRowIndex, tempColIndex As Long
Application.ScreenUpdating = False
Set myRange = Sheets("Sheet1").Range("SortRangeValue")
rowStartIndex = myRange.Row
colStartIndex = myRange.Column
colCount = myRange.Columns.Count
rowCount = myRange.Rows.Count
Dim tempCal As Long
tempCal = rowCount + rowStartIndex - 2

tempRowIndex = 6
tempColIndex = 200
Range(Cells(rowStartIndex, colStartIndex), Cells(tempCal, colStartIndex)).Copy Destination:=Range(Cells(tempRowIndex, tempColIndex), Cells(tempRowIndex + tempCal, tempColIndex))
     For iIndex = 0 To rowCount - 3 Step 1
        For jIndex = 0 To rowCount - iIndex - 3 Step 1
            rowIndex = jIndex + tempRowIndex
            current = Cells(rowIndex, tempColIndex)
            currentPlusOne = Cells(rowIndex + 1, tempColIndex)
            If current > currentPlusOne Then
            Cells(rowIndex, tempColIndex) = currentPlusOne
            Cells(rowIndex + 1, tempColIndex) = current
            End If
       Next jIndex
     Next iIndex

     Dim tempFinalRowIndex, tempFinalColIndex As Long
     tempFinalRowIndex = 6
     tempFinalColIndex = 201

     Dim orgRange, tempRange As Long
     For iIndex = 0 To rowCount - 2 Step 1
        rowIndex = iIndex + tempRowIndex
        tempRange = Cells(rowIndex, tempColIndex)
        'MsgBox (tempRange)
            For jIndex = 0 To rowCount - 2 Step 1
                rowIndex = jIndex + rowStartIndex
                orgRange = Cells(rowIndex, colStartIndex)

                If tempRange = orgRange Then
                    'MsgBox ("Match Found : \n (tempRange,orgRange) : (" & tempRange & "," & orgRange & ")")

                   Range(Cells(rowIndex, colStartIndex), Cells(rowIndex, colStartIndex + colCount - 1)).Copy Destination:=Cells(tempFinalRowIndex + iIndex, tempFinalColIndex)
              End If
          Next jIndex
       Next iIndex

    Application.ScreenUpdating = True
    Range(Cells(tempFinalRowIndex, tempFinalColIndex), Cells(tempFinalRowIndex + rowCount - 2, tempFinalColIndex + colCount - 1)).Copy Destination:=Range(Cells(rowStartIndex, colStartIndex), Cells(rowStartIndex + rowCount - 2, colStartIndex + colCount - 1))
    Range(Cells(tempFinalRowIndex - 1, tempFinalColIndex), Cells(tempFinalRowIndex + rowCount - 2, tempFinalColIndex + colCount - 1)).Delete
    Range(Cells(tempRowIndex, tempColIndex), Cells(tempRowIndex + rowCount - 2, tempColIndex)).Delete
End Sub

Answer 4

Use Google Sheets. The sort and filter works exactly the same way but it doesn't give you an error when you want to do that.