How do I sort a table w/ merged cells

J

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
 
T

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
 
J

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?
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top