Excel 2003 - Merged Cells Size Restriction When Sorting

[Hack]

I suspect the answer to this is No, but I want to check anyway.

I have a report that has been dumped to Excel and formatted using Merged Cells.

I need to sort a number column in this report, but it won't let me because the size of the merged cells do not match. I tried unmerging the cells, but that totally messes up all the formating.

Is there any slick trick way around the merged cells size restriction with respect to column sorting?

[Siddharth Rout]

I suspect the answer to this is No, but I want to check anyway.

Yes there is a way. Using VBA code... Would that help?

[Hack]

Yes there is a way. Using VBA code... Would that help?

I could put a button on the report, run some code, and then just delete the button, so yeah...if the VBA code did what I needed, and maintained the existing formatting, then that should work.

What slick trick do you have up your sleeve?

[Siddharth Rout]

What slick trick do you have up your sleeve?

Ha Ha

1) Put the values of let's say Col "A" in an Array
2) Sort the array
3) Put them back

Lemme know if you want an example?

[Hack]

Well, I can do that...but I have data in columns A through I

Colums D,E,and F are merged as are J,K,L and M

C: is a dollar column

A: is a text column which corresonds to the dollar amount in column C:

I need C: sorted in descending order, but it has to maintain its relationship with the rest of the columns so just sorting the one column would kind of mess things up.

I can remove all sensitive information and attach a generic example if you want.

[Siddharth Rout]

I can remove all sensitive information and attach a generic example if you want.

Perfect

[Hack]

I need "Drug Payments" sorted in descending order.

Thanks...if you can pull this off you will be on my Christmas Card List

[Siddharth Rout]

Something like this?

If the format changes then the vba code needs to be changed as well...

I have created a 'Test Sheet' so that the 'Original Sheet' can be used to compare with the 'Test Sheet'.

Sid

Edit:
I tested it again... so don't forget to put perfume on the card....

[Hack]

I'm not making out my card list just yet.

Seriously...I know Sort is a valid property so I'm not sure why this is happening.

[Siddharth Rout]

Give me 15 mins... I think I know what the problem is...

Sid

[Siddharth Rout]

Update the Zip file

[Hack]

Update the Zip file

Update it with what?

[Siddharth Rout]

My Bad... I replaced the zip file (wanted to say updated the file)

[Hack]

Hmmm...the drug name gets sorted, but the drug payment does not.

[Siddharth Rout]

5 mins... let me recheck

Edit:
How stupid of me... Hmph!!!! I have Updated the Zip File

[Hack]

All of the columns sort together, but as you can see the numbers are not in order.

Maybe this is an Excel thing.

[Siddharth Rout]

What is your sort criteria?

DRUG NAME - Descending

and then? (you can suggest Two more)

[Hack]

The user wants the drug payment amount sorted descending as criteria 1 and drug name goes with the payment amount as do all the rest of the columns.

I'm thinkin' I might have been right and this just isn't gonna happen.

I'll play around with it a bit.

[Siddharth Rout]

I'm thinkin' I might have been right and this just isn't gonna happen.

Hack, Excel gives you 3 criteria to sort on. based on your above query, I had sorted it only on 1 criteria i.e the drug name. Can you tell me the third one?

1st is drug payment amount
2nd is drug name

Which is the 3rd?

[anhn]

This code takes under 0.7 second on my PC (and it won't cost you a cent.)

Sub SortMerge()
    Dim r1 As Long
    Dim r2 As Long
    Dim t  As Single: t = Timer
    
    With Sheet1
        On Error Resume Next
        r1 = WorksheetFunction.Match("DRUG NAME", .Columns(1), 0)
        If r1 = 0 Then MsgBox "Header ""DRUG NAME"" not found.": Exit Sub
        r2 = WorksheetFunction.Match("GRAND TOTAL", .Columns(1), 0) - 2
        If r2 = 0 Then MsgBox """GRAND TOTAL"" not found.": Exit Sub
        On Error GoTo 0
        Application.ScreenUpdating = False
        With .Range("A" & r1 & ":M" & r2)
            '-- need to unmerge before sorting
            .UnMerge
            '-- don't care about DRUG NAME column: sort by "DRUG PAYMENTS" only
            .Sort Key1:=.Cells(1, 3), Order1:=xlDescending, Header:=xlYes
            '-- re-merge across
            .Columns("A:B").Merge Across:=True
            .Columns("C:F").Merge Across:=True
            .Columns("I:M").Merge Across:=True
        End With
        Application.ScreenUpdating = True
        MsgBox "Done!" & vbCrLf & Format(Timer - t, "0.000") & " secs."
    End With
End Sub

[Siddharth Rout]

I feel you gave up too soon!

What you want is absolutely achievable. See post 19

Sid