Problem with merged cells

[Dave Small]

I've encountered a problem when copying and pasting the contents of one
worksheet into another similar worksheet. The columns are all the same
in both worksheets.

After pasting I'm trying to sort and am getting an error message that
"Merged cells must all be the same size to sort?" I didn't want any
merged cells but there are some being created through the copy/paste
process.

My questions:

Is there a way to prohibit merged cells so that they simply can't exist?

Is there a way to search a worksheet to find merged cells?

Is there another way to combine worksheets that would avoid this problem?

 

[JE McGimpsey]

Is there a way to prohibit merged cells so that they simply can't exist?

Would that they could - I would do away with them myself. I once wrote
an add-in to delete them, but there's no event that fires when a cell is
merged, so one has to use the Calculate event or an OnTime macro - both
of which take a lot of processor power.

Is there a way to search a worksheet to find merged cells?

You can use this macro (which could be made more efficient, by checking
alternate cells, but it's still fairly fast):

Public Sub FindMergedCells()
Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange
If rCell.MergeCells Then
rCell.Select
Exit Sub
End If
Next rCell
MsgBox "No merged cells found."
End Sub


One way to remove all merged cells in a sheet at once:

Select all cells in the sheet. Choose Format/Cells/Alignment. If there
are merged cells, the Merge checkbox will have a horizontal line through
it. Click it once to check it, then once more to clear it, then click OK.

This macro does the same thing:

Public Sub RemoveMergedCells()
ActiveSheet.UsedRange.MergeCells = False
End Sub

Is there another way to combine worksheets that would avoid this problem?

You can use a macro to remove merged cells either before or after
combining the worksheets.