Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在Excel中識別並選擇所有合併的單元格？

您知道如何在Excel中查找和選擇所有合併的單元格嗎？ 這是快速識別和選擇Microsoft Excel 2007和2010中選定範圍內的所有合併單元格的巧妙方法。

使用“查找”命令識別並選擇所有合併的單元格

用VBA代碼識別所有合併的單元格

使用Kutools for Excel選擇併計算所有合併的單元格

箭頭藍色右氣泡使用“查找”命令識別並選擇所有合併的單元格

您可以通過以下方式識別並選擇活動工作表中的所有合併單元格： 發現 命令，並執行以下步驟：

1。 點擊 主頁 > 查找和選擇 > 發現 打開 查找和替換 對話框。 您也可以打開 查找和替換 對話框，按 Ctrl + F 鍵。

2。 點擊 格式 對話框中的按鈕，（如果找不到 格式 按鈕，請點擊 選項 按鈕以展開對話框。）請參見屏幕截圖：

doc選擇合併的單元格1

3。 在彈出 查找格式 對話框中，僅選中 合併單元格 在選項 文字控制 根據第 校準 選項卡，然後單擊 OK.

doc選擇合併的單元格2

4。 現在您回到 查找和替換 對話框，單擊 找到所有 按鈕。 所有合併的單元格都在此對話框的底部列出。 按住，選擇所有查找結果 轉移 鍵。

現在，當您選擇所有查找結果時，將選中活動工作表中的所有合併單元格。 看截圖：

doc選擇合併的單元格3

提示： 如果您只想識別，查找並選擇所選內容中的合併單元格，則需要首先選擇範圍。

箭頭藍色右氣泡用VBA代碼識別所有合併的單元格

VBA 1：識別並突出顯示所有合併的單元格

1。 按住 ALT + F11 鍵，然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊，然後將以下宏粘貼到 模塊 窗口。

Sub FindMergedcells()
'updateby Extendoffice
Dim x As Range
For Each x In ActiveSheet.UsedRange
If x.MergeCells Then
x.Interior.ColorIndex = 8
End If
Next
End Sub

3。 按 F5 運行此宏的鍵。 標識並突出顯示活動工作表中的所有合併單元格，請參見屏幕截圖：

doc選擇合併的單元格4

VBA 2：識別並列出所有合併的單元格

1。 按住 ALT + F11 鍵，然後打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊，然後將以下宏粘貼到 模塊 窗口。

Sub ListMergedcells()
'updateby Extendoffice
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。 按 F5 鍵以運行此宏，所有合併的單元格都在彈出對話框中列出。 看截圖：

doc選擇合併的單元格5

箭頭藍色右氣泡 使用Kutools for Excel選擇併計算所有合併的單元格

Kutools for Excel's 選擇合併的單元格 該工具將幫助您一鍵確定，找到並選擇所有合併單元格。

Kutools for Excel : 帶有300多個便捷的Excel加載項，可以在30天內免費試用. 
前往下載
30天免費試用购买
貝寶/ MyCommerce

安裝後 Kutools for Excel，請執行以下操作： 立即免費下載Kutools for Excel！ )

1。 選擇要選擇合併單元格的數據范圍。

2。 點擊 Kutools > 選擇 > 選擇合併的單元格，請參見屏幕截圖：

3。 並且一次選擇了所有合併的單元格，並且合併的單元格數也被計數，請參見屏幕截圖：

doc選擇合併的單元格7

立即免費下載Kutools for Excel！

最佳辦公效率工具

Kutools for Excel解決了您的大多數問題，並使您的生產率提高了80％

  • 重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據； 拆分單元格內容； 合併重複的行/列...防止細胞重複； 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
  • 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
  • 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
  • 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
  • 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
kte選項卡201905

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50％，並減少數百次鼠標單擊！
officetab底部
Say something here...
symbols left.
You are guest
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.
    Natalia · 6 years ago
    Is it possible to identify the first and the last column number of the merged range in vba?
  • To post as a guest, your comment is unpublished.
    Ivor Shaer · 6 years ago
    I 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.
    Senthooran · 6 years ago
    well done You are a star...
    Thanks :-)
  • To post as a guest, your comment is unpublished.
    srinivasan · 6 years ago
    Very Nice thanks a lot
  • To post as a guest, your comment is unpublished.
    JEAN · 7 years ago
    Actually 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.
    Chamundi · 7 years ago
    Probably 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.
    pavithraravindar · 7 years ago
    thank u this help me to find merged cell in my excel
  • To post as a guest, your comment is unpublished.
    Chamundi · 7 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.
    Chamundi · 7 years ago
    Actually 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.
    Chamundi · 7 years ago
    If 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.
    Roy · 7 years ago
    This 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.
    Chamundi Sabanathan · 7 years ago
    I 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?