How do I sort a table w/ merged cells

[John]

I have a 12 column table (cols B -M). Each row is formated the same and the
last 8 columns of each row (cols F thru M, all having a width of 9) are
merged into single cells in the row.

What code could I use to place the rows in alphabetic order, first by col C
and then by col B?

Whenever I try to sort this table manually using the Sort option on the Data
Ribbon, I get the error: This operation requires the merged cells to be
identically sized.

I appreciate your help, -John

 

[Tom Hutchins]

Unmerge the cells in columns F-M, and set the column width of F to 72. Then
sort normally. All the data is in column F anyway; columns G-M are empty.

Hope this helps,

Hutch

 

[John]

Tom, Good idea except my boss won't be too happy when I push all his summary
stats way to the right on the worksheet w/ the table! The rows above the
table contain a bunch of columns of summary data and the table spans these
rows. I had to merge all these cells in table rows in order to compensate
and do the table like my boss wants. There is a similar problem w/ data
below the table. I can't move the table to the right either since it has to
print in line w/ all the other data.

Sooo. How do I sort a table containing merged cells?

 

[Tom Hutchins]

Sorry if this posts twice. One way is to unmerge the cells, sort the table,
then re-merge the cells. Here is an example:

Sub SortTable()
Dim Tbl As Range
'Start with a cell in the table selected
'Select all the cells in the current region
Selection.CurrentRegion.Select
Set Tbl = Selection
'Unmerge all merged cells.
Selection.UnMerge
'Now sort the table
Tbl.Sort Key1:=Range("A5"), Key2:=Range("B5"), Header:=xlYes
'Walk down through each row of the table & re-merge cols F-M
Tbl.Cells(1, 1).Activate
Do While Not Intersect(ActiveCell, Tbl) Is Nothing
Range("F" & ActiveCell.Row & ":M" & ActiveCell.Row).Merge
ActiveCell.Offset(1, 0).Activate
Loop
'Free the object variable
Set Tbl = Nothing
End Sub

You would have to edit the Sort command parameters, of course. You also may
need to use something other than CurrentRegion to specify the table range.

Hutch