Excel tutorials

Article Index

How to Quickly Select a Specific Address a Merged Cell in Excel

In case you have a lot of data in the worksheet and in addition worksheet contains dozens of merged cell, the question is how to quickly position on a specific cell merged. The solution lies in the 'Find All' button. Clicking this button in the dialog box 'Find and Replace' will appear a list of all the cell references that are first in merged cells. Clicking on any of the hyperlinks Excel you automatically positioned to address respective merged cell

How to quickly position on a particular merged cells in Excel 

You notice in the picture above list on the Worksheet (Sheet) and in the picture below you will see a list inside the Workbook. This list is determined by the choices in the 'Within', in which the drop down menu select Sheet or Workbook. 

 How to create a list of all the hyperlinks on the merged cells in Excel workbook

Find all merged cells and highlight it by color

If you want to find all the merged cells in the worksheet and mark them color then use the VBA macro below. 

Use VBA to find and coloring all the merged cells 

VBA macro for a solution in the image above is as follows. This VBA macro to copy the 'Sheet Module'. Save the workbook as *.xlsm format to support execution of VBA.

Sub FindMergedCellsAndColorIt()
'finds all cells that are merged and paint them the color
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then
c.Interior.ColorIndex = 3 'number 3 for red color
End If
Next
End Sub

How to Numbering Merged Cells in Excel

If you want to number the merged cells in the row or column, then use this VBA macro below.

 How to numbering merged cells in Excel

If you want numbering merged cells in Columns replace (change) the piece of code that refers to the OFFSET command

'Option Explicit
Sub NumberingMergedCellsInRow()
'numbering merged cells in Columns
Dim b As Integer
Dim c As Integer
Dim TheEnd As String
b = 0
TheEnd = InputBox("Enter up to any number you want to make a numbering:" & Chr(13) & "(must be entered a whole number)", "Numbering")
If TheEnd = 0 Or TheEnd = "" Then: Exit Sub
Do
Range("A1").Offset(b, 0).Select 'For the numbering of the Columns of cells merged replacing Offset (b, 0) in the offset (0, b)
a = Selection.Cells.Count
If a = 1 Then
b = b + 1
ElseIf a > 1 Then
b = b + a
End If
c = c + 1
Range("A1").Offset(b, 0) = c 'For the numbering of the Columns of cells merged replacing Offset (b, 0) in the offset (0, b)
If c = TheEnd Then: Exit Do
Loop
Range("A1").Select
End Sub

Merge Excel Cells Without Loss Data

If you need sometimes merge multiple cells into one and the respective cells contain specific information. Of course, you do not want to lose the respective data but you want data from multiple cells are merged in a one cell. The solution to this merger cells with no lost data see in the picture below. 

 Merge Excel cells without data loss

Take the example in the picture above. You note in column 'A' text data (name company employees). You want all the names merge into one cell. Given that, 'names' at the end of words have no sign that we used for the 'Find and Replace' that you may add a 'comma' and ultimately had separate words, take advantage'll formula in column 'B' which reads =A2&", " or =CONCATENATE(A2;", ") and copy down.

Copy the cell in column 'C' in the column 'D' on the way to paste copied in column 'D' as the Paste Special => Value.

Spread the column 'D' on wide enough to fit all the words (names) after the merger.

Select the cell in column 'D'.

Then click the ribbon tab Home => Fill => Justify

Now at the end you have all the words (names) in a single cell, if you think that the cells are too wide, format it as 'Wrap Text'. (VBA source unknown, I do not remember, just Internet Networks)