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 formatcellsalignment
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