Sorting Area with Merged Cells

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)


"prizm1" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''

Peo Sjoblom wrote:
> 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
>
>

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.


prizm1 wrote:
>
> Peo Sjoblom wrote:
> > 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
> >
> >
>
> 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?

--

Dave Peterson

Peo Sjoblom wrote:
> 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
>
>

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.


prizm1 wrote:
>
> Peo Sjoblom wrote:
> > 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
> >
> >
>
> 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?

--

Dave Peterson