How to automatically merge blank cells above/left in Excel?

In this article, I am going to talking about merging blank cells above or left automatically as below screenshot shown. Actually, there is no built-in utility that can handle this task, but the macro codes can.

Merge blanks above

Merge blanks left

Merge blank cells above based on a column

Merge blank cells above (only work for single column)

Merge blank cells left

---

Merge blank cells above based on a column

---

Supposing there is a range of data in three columns, and now you want to merge the data above based on column C.

1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window, and click Insert > Module. See screenshot:

2. Then paste below code to the blank script. See screenshot:

VBA: Merge blank above based on next column

Sub MergeCells()
'UpdatebyExtendoffice2017025
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Select a range:", "KuTools For Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    For Each xCell In xRg
        If xCell.Value = "" Then
            Range(xCell, xCell.Offset(-1, 0)).Merge
        End If
    Next
End Sub

3. Press F5 key to run the code, and then a dialog pops out to remind you to select a range to work. See screenshot:

4. Click OK, then the blank cells have been merged above. See screenshot:

---

Merge blank cells above (only work for single column)

Here is a macro code that can merge the blank cells above in the specified column.

1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window, and click Insert > Module. See screenshot:

2. Paste below code to the script. See screenshot:

VBA: Merge blank cells above

Sub mergeblankswithabove()
'UpdatebyExtendoffice20171025
    Dim I As Long
    Dim xRow As Long
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Select a range (single column):", "KuTools For Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Columns.Count > 1 Then
        MsgBox "Only work for single column", , "KuTools For Excel"
        Exit Sub
    End If
    xRow = xRg.Rows.Count
    Set xRg = xRg(xRow)
    For I = xRow To 1 Step -1
        Set xCell = xRg.Offset(I - xRow, 0)
        Debug.Print xCell.Address
        If xCell.Value = "" Then Range(xCell, xCell.Offset(-1, 0)).Merge
    Next
End Sub

3. Press F5 key to run the code, and a dialog pops out for you to select a column range. See screenshot:

4. Click OK. And the blank cells in the selection have been merged above.

---

Merge blank cells left

If you want to merge blank cells left, the following code can do you a favor.

1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window, and click Insert > Module. See screenshot:

2. Then paste below code to the blank script. See screenshot:

VBA: Merge blanks left

Sub mergeblankswithleft()
'UpdatebyExtendoffice20171025
    Dim xRg As Range
    Dim xCell As Range
    Dim xAddress As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Select a range:", "KuTools For Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    For Each xCell In xRg
        If xCell.Value = "" Then Range(xCell, xCell.Offset(0, -1)).Merge
    Next
End Sub

3. Press F5 key to run the code, and a dialog pops out for you to select a range. See screenshot:

4. Click OK. The blank cells have been merged left.

Merge blanks left

---

Merge same cells or unmerge cell

Go to Download Free Trial 60 daysPurchase PayPal / MyCommerce

---

---

Recommended Productivity Tools for Excel