·
4 years ago
Is it possible to identify the first and the last column number of the merged range in vba?
How to identify and select all merged cells in Excel?
Do you know how to find and select all merged cells in Excel? Here is the cool tricky ways to identify and select all merged cells in a selection or range in Microsoft Excel 2007 and 2010 quickly.
Identify and select all merged cells with Find command
Identify all merged cells with VBA code
Select and count all merged cells with Kutools for Excel
Select and count all merged cells in worksheet:
Kutools for Excel’s Select Merged Cells feature can help you to find and select all merged cells in a selection or a worksheet and get the number of merged cells as well.
Identify and select all merged cells with Find command
You can identify and select all merged cells in active worksheet by Find command with following steps:
1. Click the Home > Find & Select > Find to open the Find and Replace dialog box. You can also open the Find and Replace dialog box with pressing the Ctrl + F keys.
2. Click the Format button in the dialog box, (If you can't find out the Format button, please click the Options button to expand the dialog.) see screenshot:
3. In the popping up Find Format dialog box, only check the Merge Cells option in the Text control section under Alignment tab, and click OK.
4. Now you go back to the Find and Replace dialog box, click Find All button. All merged cells are listed at the bottom of this dialog box. Select all finding results with holding down the Shift key.
Now all merged cells in active sheet are selected when you select all finding results. See screenshot:
Tips: If you want to only identify, find, and select merged cells in a selection, you need to select the range first.
Identify all merged cells with VBA code
VBA 1: Identify and highlight all merged cells
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
Sub FindMergedcells() 'updateby Extendoffice 20160106 Dim x As Range For Each x In ActiveSheet.UsedRange If x.MergeCells Then x.Interior.ColorIndex = 8 End If Next End Sub
3. Press the F5 key to run this macro. All merged cells in active worksheet are identified and highlighted, see screenshot:
VBA 2: Identify and list all merged cells
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
Sub ListMergedcells() 'updateby Extendoffice 20160106 Dim x As Range Dim sMsg As String sMsg = "" For Each x In ActiveSheet.UsedRange If x.MergeCells Then If sMsg = "" Then sMsg = "Merged cells:" & vbCr End If sMsg = sMsg & Replace(x.Address, "$", "") & vbCr End If Next If sMsg = "" Then sMsg = "No merged cells." End If MsgBox sMsg End Sub
3. Press the F5 key to run this macro, all merged cells are listed in a popping up dialog box. See screenshot:
Select and count all merged cells with Kutools for Excel
Kutools for Excel's Select Merged Cells tool will help you identity, find and selected all merged cells in a selection with only one click.
| Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. |
After installing Kutools for Excel, please do as follows:( Free Download Kutools for Excel Now! )
1. Select the data range that you want to select the merged cells.
2. Click Kutools > Select > Select Merged Cells, see screenshot:
3. And all the merged cells in the selection have been selected at once, and the number of the merged cells are counted as well, see screenshot:
Free Download Kutools for Excel Now!
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
· 4 years agoI require code to list merged ranges in a worksheet where the merged ranges are individually entered in cells starting at "A1" thus
a3:c3
b2:b7
etc........... -
To post as a guest, your comment is unpublished.
· 5 years agowell done You are a star...
Thanks :-) -
To post as a guest, your comment is unpublished.
· 5 years agoVery Nice thanks a lot
-
To post as a guest, your comment is unpublished.
· 5 years agoActually I just about went mental trying to fix this in a spreadsheet. In desperation I selected all the cells (control A) clicked "merge and centre" and presto! it was fixed.
-
To post as a guest, your comment is unpublished.
· 5 years agoProbably obvious, but: In my last comment I should have made it clear you need to "select" each column, in turn, before scanning visually. Though really you only need to scan certain columns, I think: To the best of my knowledge, it's only cells containing text that will occasionally annex an adjoining cell, and numeric-only columns can be trusted not to do so.
Even with text columns, you should be OK just checking every other column, because if any cell in the selected column has been involved in an annexation (to the right or from the left), that will show up in a visual scan of the selected column.
I have never seen annexations occurring vertically, only horizontally. But if such a thing happened (a vertical annexation), you could try the same technique going row by row instead of column by column.
The procedure is tedious, definitely. A royal pain, in fact. But if you have to sort your data, and Microsoft refuses to fix their bug, it's the only recourse I know of. Nowadays I try to remember to put a space character into each cell of the area I expect to use, prior to entering any other data, thus ensuring no annexations will occur. -
To post as a guest, your comment is unpublished.
· 5 years agothank u this help me to find merged cell in my excel
-
To post as a guest, your comment is unpublished.
· 5 years ago... so in the spreadsheet you spoke of, which was not set up with those protective space characters, my approach would be to visually scan each column which lies just to the right of any text column; and immediately after identifying & unmerging each occurrence, I would put a space character in the empty cell so the merging will not recur.
Probably there's a VBA or other coding means to accomplish this much more efficiently. Anyone??? -
To post as a guest, your comment is unpublished.
· 5 years agoActually I think this can be avoided entirely, if you remember to do so before entering data into any text column.
In my experience the only time cells are clandestinely merged is when an empty cell is to the right of a text cell, where normally the display of the text would be extended to take advantage of the otherwise unused display space provided by the empty cell.
Therefore, when initially setting up your spreadsheet, before entering any data, you can fill every "susceptible" cell with a single space—as many rows down as you expect to have data to fill. That space will be honored like any other text, and the cell to the left will not annex it. -
To post as a guest, your comment is unpublished.
· 5 years agoIf your spreadsheet is small (or you are desperate enough), the best way I've found is to select one column at a time and scroll all the way down to the bottom. Any merged cells will be obvious, because the entire merged cell is highlighted. You can then fix each one, one by one.
But you risk wasting a lot of time doing this, since Excel continues to merge cells "behind your back" whenever it feels like doing so.* Therefore, cells you have just unmerged (or others which hadn't been merged before) may become merged while you believe you are finishing the unmerging process.
I tried to find a way to completely disable the merging of cells but haven't found it. Better, of course, would be some way to keep Excel from engaging in this psychopathic behavior!
*Yesterday, desperate, I did try to unmerge cells in a not-so-large spreadsheet (22 columns and fewer than 1,000 rows). Each time I thought I had finished and tried to sort, I got that same message. So then I tried another way to identify where the merged cells were—selecting a screenful of rows at a time and trying the sort on just those rows. Each time I got the message, I would try half the screenful at a time (etc.) until I identified the row(s) with merged cells. By going through the entire spreadsheet until each screenful had been successfully sorted, I figured the entire sort should work. But, NOT. Excel had been gleefully merging cells I had just unmerged.
Please, someone, post a solution! -
To post as a guest, your comment is unpublished.
· 5 years agoThis is an incredibly complex process that is way over my head for Excel. All I am looking to do is find out which cells are merged so I can do a sort. I received a message that said merged cells need to be the same size before I can sort and I don't know which ones are merged.
-
To post as a guest, your comment is unpublished.
· 5 years agoI have Excel 12.3.6 for Mac and it frequently takes it upon itself to merge cells. Then I cannot sort without identifying and unmerging each merged cell. In a large spreadsheet this is impractical. Should be able to use the "Merge" button in the Toolbox to unmerge all cells in a selected range, rather than just one at a time! Anyone else have this problem? or even better, a solution?
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
한국어
Português
Русский
Español