Data sort problem in Excel

  Newuser697 18:45 12 Aug 07
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 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

Ralph

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…