AutoFill Table Data Gaps Excel VBA Macro

Sometimes we need to manually create a table with many repeating terms and is time-consuming to use Excel’s AutoFill/FillDown or Go To Special features to fill the gaps. We can easily do that with Excel VBA. This macro example fills table gaps with values above within each column in the selected range.

Macro code:

 

  Sub AutoFillTableData()

      Dim SelRow As Range, SelCol As Range, FillValue As Variant

 

      'Check column by column within selection

      For Each SelCol In Selection.Columns

   

           'Loop through all rows in selection

           For Each SelRow In Selection.Rows

     

                'Check for value to fill

                If Cells(SelRow.Row, SelCol.Column).Value <> "" Then

                      FillValue = Cells(SelRow.Row, SelCol.Column).Value

                Else

                       Cells(SelRow.Row, SelCol.Column).Value = FillValue

                End If

   

            Next SelRow

      Next SelCol 

End Sub

 

Macro explained:

• First we declare two range object variables (SelRow and SelCol) to assign the selected row and column, respectively, while looping through the selection. We declare FillValue as a variant, as that will get any type of data found for each cell in the selection.
• Then we use two For Each loops in order to loop through every cell (with rows), within each column in the selection. The loop spans as many rows and columns as there are in the selection, i.e. Selection.Rows and Selection.Columns.
• Finally, we assign the value of each cell with content to the variable FillValue, and then assigns that value to all the empty following cells within the same column. The same process repeats for each column.

This is how we autofill table data gaps in Excel using VBA macros.

 

Other examples:

• How to Combine All Open Workbooks with Excel VBA macros
  
• How to Compare Two Worksheets with Excel VBA macros 
  
• How to Send Email From Excel with Excel VBA macros