Data sort problem in Excel

  Newuser697 18:45 12 Aug 07

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


  VoG II 19:01 12 Aug 07

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 07

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

  Newuser697 19:25 12 Aug 07

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


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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation technologies coming to Siggraph 2017

iPad Pro 12.9 vs Surface Pro 5