Modifying the advice already given, this selects merged cells in any area you select.
Sub findmerged()
Dim str As String
Dim stri As String
For Each c In Selection
If c.MergeCells Then
str = c.Address
stri = stri & "," & str
End If
Next
stri = "(" & Mid(stri, 2, 1000) & ")"
Range(stri).Select
End Sub
1. Open a Find and Replace Menu.
2. Next to the Find What Field is a Format Box, Click it.
3. This opens the Find Format Menu screen which looks very similar to the Cell Format Tabs and screens
4. On the Alignment Tab, remove any ticks in the Wrap Text or Shrink to Fit boxes.
5. Leave a tick in the Merge Cells Box and click the OK Button to return to the Find and Replace screen.
6. Now click the Find All Button and ALL merged cells are shown in the lower window and are selectable to jump to their location.
I was surprised by your response after all this time; however, I am thankful for your reply. I have asked a lot of “so called” gurus and none had the answer.
I am curious as to how you acquired this particular skill.
I am very good at using Excel, and I had a request at work today to find a merged cell in a big spreadsheet and found this method elsewhere on the web, I cant remember where.
All the gurus seem to like immediately going for some code to solve every problem when sometimes the software already has a method of doing the task.
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.