Locate merged cells to sort on 3 columns

[Rob B]

Repeated attempts to locate merged cells have failed. Get message when trying
to sort data with priority on 3 columns: "This operation requires the merged
cells to be identically sized"

How can this be done? I have tried copying & pastng vaulues only; am at wits
end. Size of datset: 600+rows x 52 columns

 

[Dave Peterson]

Do you want to locate the merged cells or do you want to remove them?

If you want to remove the merged cells, then select the range to fix (all the
cells on the worksheet is ok with me!).

Then Format|Cells|alignment tab
clear that merge cells checkbox.

If you really only wanted to determine where they were, you could use a divide
an conquer approach.

Select a range
show alignment tab and check that "merge cells" checkbox.

If it's clear, then there are no merged cells in that selection.

If it's not clear, then reduce the size of the range (by half) and check each
half again. (repeat until you've checked all the cells you're interested in.

Or you could use a macro:

Option Explicit
Sub testme()

Dim myCell As Range
Dim resp As Long

For Each myCell In ActiveSheet.UsedRange.Cells
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
resp = MsgBox(prompt:="found: " _
& myCell.MergeArea.Address & vbLf & _
"Continue looking", Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If
End If
End If
Next myCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Luke M]

As it sounds like you are not wanting merged cells, what you could do would
be to select all, then format cells. Under alignment, make sure the "merge
cells" box is unchecked. Ok out.

You should now be able to sort.