Data sort problem in Excel

  Newuser697 18:45 12 Aug 2007
Locked

I have a spreadsheet with a league table as one of the work sheets in Microsoft Excel '97.
When trying to sort the table into order in the league for highest points, I get a message stating "to perform this operation, all merged cells must be of the exact same size"
I formatted all the columns that I needed to sort to the same width, but I still get the same message.
Does anyone have any ideas?

Thanks in advance

Ralph

  VoG II 19:01 12 Aug 2007

Merged cells are the spawn of the devil and should be avoided at all costs (as you have discovered). There are other ways of formatting (center across selection) to give the same visual effect. To be able to sort you will need to lose (i.e. unmerge) the merged cells.

If you haven't deliberately merged any cells, you can use the following macro.

Sub test()
Dim c As Range, mcells As Long
mcells = 0
For Each c In ActiveSheet.UsedRange
If c.MergeCells = True Then
mcells = mcells + 1
MsgBox c.Address(False, False) & " is merged"
End If
Next c
MsgBox "Total of " & mcells & " merged cells found"
End Sub


To use this, press ALT + F11 to open the Visual Basic Editor. Insert > Module then copy the above code and paste it in. Close the VBE. On your sheet Tools > Macro > Macros, highlight 'test' and click the run button.

  VoG II 19:02 12 Aug 2007

I should have said 'you can use the following macro to list the merged cells'.

  Newuser697 19:25 12 Aug 2007

Grateful thanks VOG
I did as suggested and unmerged all the cells (although they were in fact the same size)
The data sort then worked perfectly.
I had to get the results out tonight by e-mail
and face my work colleagues tomorrow.
You saved me some embarrassment.

Thanks again

Ralph

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Comment afficher des fichiers cachés sur Mac ?