AutoFill Table With SpecialCells Excel VBA Macro

In a previous post we have seen how to autofill table gaps in Excel using VBA macros. In this post we see another way to do it with less code. This macro example uses the CurrentRegion property to select the table, the SpecialCells property to target the gaps only, and the Offset property to get the value to be filled.

Macro code:

 

  Sub AutoFillTableWithSpecialCells()

 

      Dim cell As Range

 

      'loop through table blanks

      For Each cell In Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)

     

          cell.Value = cell.Offset(-1, 0).Value  'take the value above

   

      Next cell

 

  End Sub

 

Macro explained:

• In this example we just declare an object variable (cell) as a Range, that it is going to be used to loop through each cell in the table range.
• Then we start a For Each loop in order to loop through every empty cell in the table. We determine the table range using the CurrentRegion property. That sets the range to the region with data around cell A1. For that range, we specify to loop through blank cells only using the SpecialCells property.
• SpecialCells allows to target cells with several types of cell values or formatting. Among some of the possibilities are cells with formulas, with comments, with validation, and many more, and of course, blank cells, which is the types of cells we are selecting in this example.

• Finally, we get the value from the cell above using the Offset property and setting to -1 the row offset. We set the column offset to 0, as the fill in value is taken from the cell above within the same column.

This is how we autofill table with special cells in Excel using VBA macros.

Other examples:

• How to Resize All Charts In Worksheet with Excel VBA macros
  
• How to Split Text Into Columns with Excel VBA macros
  
• How to Insert Picture From URL with Excel VBA macros