Sorting Area with Merged Cells

  • Thread starter Thread starter prizm1
  • Start date Start date
P

prizm1

In the area A23:G30, each row contains merged cells of column B, C, D, E
and F apart from each other row in the area. I get a "This operation
requires that merged cells to be identically sized" error when running
this macro. Even using the Data/Sort from the File menu gives me this error.

How would I sort an area containing rows with merged cells? Also, the
cells must remain (or return to being) merged after this operation.

I appreciate the previous help concerning clearing the contents from
merged cells using ActiveCell relative references. Unfortunately, my
email/newsgroup client, for whatever reason, will not display that
previously posted newsgroup thread, so I must start another. Will fix.

Also, the Header parameter line is not broken in my code but just
displays that way in this message formatting.

'''''''''''''''''''''''''''''''''''''''''''''''''''
Sub SortData()

Application.ScreenUpdating = False

Range("A23:G30").Select
Selection.Sort _
Key1:=Range("A23"), Order1:=xlAscending, _
Key2:=Range("F23"), Order2:=xlAscending, _
Header:=No, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

ActiveCell.Select

Application.ScreenUpdating = True

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''
 
That is a reason why not merge cells, it almost always causes problems down
the road
The only reason cells get merged is for appearance, however you can have
similar layout without using merge.
For instance instead of merging cells A1 and B1 you can select both cells,
do format>cells>alignment
then select center across selection


--
Regards,

Peo Sjoblom

(No private emails please)
 
Peo said:
That is a reason why not merge cells, it almost always causes problems
down the road
The only reason cells get merged is for appearance, however you can have
similar layout without using merge.
For instance instead of merging cells A1 and B1 you can select both
cells, do format>cells>alignment
then select center across selection
Click to expand...

This seems to work OK for me, now:

''''''''''''''''''''''''''''''''''''''''''''''''''

Sub SortData()

Dim GoBack As Range
Dim X As Integer


Application.ScreenUpdating = False

Set GoBack = ActiveCell

Range("ProbArea").Select
Selection.Sort _
Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("A2"), Order2:=xlAscending, _
Header:=No, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom


'UnMerge Merged Cells
Range("RequestArea").Select
Range("RequestArea").UnMerge

'Sort Area
Selection.Sort _
Key1:=Range("A23"), Order1:=xlAscending, _
Key2:=Range("F23"), Order2:=xlAscending, _
key3:=Range("B23"), order3:=xlAscending, _
Header:=No, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

'Re-Merge Select Cells
For X = 23 To 30
Range("B" & X & ":" & "F" & X).Merge
Next X

GoBack.Select

Application.ScreenUpdating = True

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

BTW, how would you write a macro that would return the top and bottom
row number and left and right column letter of a named area that is not
necessarily filled with cell entries?
 
As long as your merged cells are nice and consistent, this works fine--but in
general mergedcells cause lots of trouble. (Yeah, yeah. Sometimes you just
have to use them. I do to.)

Dim myRng as range

set myrng = activesheet.range("a1:x99")
with myrng
msgbox .cells(1).row & "-" & .cells(1).column & vblf _
.cells(.cells.count).row & "-" & .cells(.cells.count).column
end with

myrng.cells(1) is the first cell in the range.
myrng.cells(myrng.cells.count) is the last cell in the range.

The with/end with just makes it easier to type.
 
Back
Top