Merged cells

[Cowtoon]

A friend of mine is trying to do a sort, but is getting the message that it
can't sort merged cells.
He's not aware of any merged cells. Is there an easy way to detect a merged
cell?
Thanks.

 

[Harlan Grove]

Cowtoon wrote...

A friend of mine is trying to do a sort, but is getting the message that it
can't sort merged cells.
He's not aware of any merged cells. Is there an easy way to detect a merged
cell?

If there aren't supposed to be any merged cells in your friend's sort
range, have him select the sort range, press [Ctrl]+[F1] to bring up
the Format Cells dialog, select the Alignment tab, and make sure the
Merge Cells checkbox is empty.

 

[Guest]

The following macro will list (via MsgBox) every merged cell on the active
sheet.

Sub ListMerged()
Dim Rng As Range, LastRng As String
LastRng$ = FindLastCell(ActiveSheet)
If LastRng$ = "ERROR" Then Exit Sub
ActiveSheet.Range("A1:" & LastRng$).Select
For Each Rng In Selection
If Rng.MergeCells Then
MsgBox Rng.Address & " is a merged cell"
End If
Next Rng
End Sub

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

Copy & paste the code above into a VBA module or the ThisWorkbook module of
the workbook. Go to the the sheet in question. Click any cell on it to make
sure it is the active sheet. Select Tools >> Macro >> Macros. Select
ListMerged and click OK.
Write down the cell addresses listed.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

 

[Cowtoon]

Thank you. I've forwarded this to my friend. If he can't figure it out ...
I probably can (will get him to send me the file). It might require several
selections to narrow the field.
Thanks again.

Cowtoon wrote...

A friend of mine is trying to do a sort, but is getting the message that it
can't sort merged cells.
He's not aware of any merged cells. Is there an easy way to detect a merged
cell?

If there aren't supposed to be any merged cells in your friend's sort
range, have him select the sort range, press [Ctrl]+[F1] to bring up
the Format Cells dialog, select the Alignment tab, and make sure the
Merge Cells checkbox is empty.

 

[Cowtoon]

Thanks Tom, it might just be what we need.
Diana

The following macro will list (via MsgBox) every merged cell on the active
sheet.

Sub ListMerged()
Dim Rng As Range, LastRng As String
LastRng$ = FindLastCell(ActiveSheet)
If LastRng$ = "ERROR" Then Exit Sub
ActiveSheet.Range("A1:" & LastRng$).Select
For Each Rng In Selection
If Rng.MergeCells Then
MsgBox Rng.Address & " is a merged cell"
End If
Next Rng
End Sub

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

Copy & paste the code above into a VBA module or the ThisWorkbook module of
the workbook. Go to the the sheet in question. Click any cell on it to make
sure it is the active sheet. Select Tools >> Macro >> Macros. Select
ListMerged and click OK.
Write down the cell addresses listed.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hope this helps,

Hutch

 

[Dave Peterson]

A small typo...

Tell your friend to use ctrl-1 (one, not F1).

Or just Format|Cells|Alignment tab from the worksheet menu tab.

Thank you. I've forwarded this to my friend. If he can't figure it out ...
I probably can (will get him to send me the file). It might require several
selections to narrow the field.
Thanks again.

Cowtoon wrote...

A friend of mine is trying to do a sort, but is getting the message that it
can't sort merged cells.
He's not aware of any merged cells. Is there an easy way to detect a merged
cell?

If there aren't supposed to be any merged cells in your friend's sort
range, have him select the sort range, press [Ctrl]+[F1] to bring up
the Format Cells dialog, select the Alignment tab, and make sure the
Merge Cells checkbox is empty.