Smart Fill in All Four Directions with VBA

Overview

Excel VBA Data Cleaning

Excel's built-in Fill Down (Ctrl+D) only copies from the cell directly above. These four macros intelligently fill blank cells in any direction by finding the nearest non-blank cell and copying its formula or value, making them perfect for cleaning merged-cell data or filling gaps in imported datasets.

Each macro uses SpecialCells(xlBlanks) to identify empty cells, then fills them with the Formula2R1C1 from the nearest populated cell in the specified direction. This preserves relative references correctly.

How They Work

Fill Down: For each column in the selection, finds blank cells and fills them with the value from the nearest cell above (End(xlUp))
Fill Up: Same approach but fills from the nearest cell below (End(xlDown)), useful for bottom-up data structures
Fill Right: Fills blank cells in each row from the nearest cell to the left (End(xlToLeft))
Fill Left: Fills from the nearest cell to the right (End(xlToRight)), completing the four-directional set

The VBA Code

Sub Fill_Down()
    Dim c_rng As Range, r_a_rng As Range, r_rng As Range
    
    If Selection.Cells.Count = 1 Then MsgBox "Select more than 1 cell": Exit Sub
    
    For Each c_rng In Selection.Columns
        On Error Resume Next
        Set r_a_rng = c_rng.SpecialCells(xlBlanks)
        For Each r_rng In r_a_rng.Areas
            r_rng.Value = r_rng.End(xlUp).Formula2R1C1
        Next r_rng
        On Error GoTo 0
    Next c_rng
End Sub

Sub Fill_Up()
    Dim c_rng As Range, r_a_rng As Range, r_rng As Range
    
    If Selection.Cells.Count = 1 Then MsgBox "Select more than 1 cell": Exit Sub
    
    For Each c_rng In Selection.Columns
        On Error Resume Next
        Set r_a_rng = c_rng.SpecialCells(xlBlanks)
        For Each r_rng In r_a_rng.Areas
            r_rng.Value = r_rng.End(xlDown).Formula2R1C1
        Next r_rng
        On Error GoTo 0
    Next c_rng
End Sub

Sub Fill_Right()
    Dim c_rng As Range, c_a_rng As Range, r_rng As Range
    
    If Selection.Cells.Count = 1 Then MsgBox "Select more than 1 cell": Exit Sub
    
    For Each r_rng In Selection.Rows
        On Error Resume Next
        Set c_a_rng = r_rng.SpecialCells(xlBlanks)
        For Each c_rng In c_a_rng.Areas
            c_rng.Value = c_rng.End(xlToLeft).Formula2R1C1
        Next c_rng
        On Error GoTo 0
    Next r_rng
End Sub

Sub Fill_Left()
    Dim c_rng As Range, c_a_rng As Range, r_rng As Range
    
    If Selection.Cells.Count = 1 Then MsgBox "Select more than 1 cell": Exit Sub
    
    For Each r_rng In Selection.Rows
        On Error Resume Next
        Set c_a_rng = r_rng.SpecialCells(xlBlanks)
        For Each c_rng In c_a_rng.Areas
            c_rng.Value = c_rng.End(xlToRight).Formula2R1C1
        Next c_rng
        On Error GoTo 0
    Next r_rng
End Sub

Final Thoughts

These fill macros are essential for data cleaning. When you receive data with merged cells or gaps from external systems, a single macro call fills all the blanks correctly. Using Formula2R1C1 ensures that formulas are copied with proper relative references.