Excel 2003 - Merged Cells Size Restriction When Sorting

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?

Quote:

---

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?

Quote:

---

Originally Posted by koolsid

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? :D

Quote:

---

Originally Posted by Hack

What slick trick do you have up your sleeve? :D

---

Ha Ha

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

Lemme know if you want an example?

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.

Quote:

---

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

---

Perfect :)

I need "Drug Payments" sorted in descending order.

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

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.... :afrog:

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

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

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

Sid

Update the Zip file

Quote:

---

Originally Posted by koolsid

Update the Zip file

---

:confused: Update it with what?

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

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

5 mins... let me recheck

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

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

Maybe this is an Excel thing.

What is your sort criteria?

DRUG NAME - Descending

and then? (you can suggest Two more)

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.

Quote:

---

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?

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

vb Code:

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

I feel you gave up too soon! :D

What you want is absolutely achievable. See post 19

Sid