Fill To The Next

Pivot tables are very useful, but later on it might get really boring filling the missing cells in the first columns. For example given a table like the one in the picture below


Before the macro.

it is very easy to write a script that will fill the gaps between the rows to obtain a table like below.

After the macro was used.

After the macro was used.

The code for the macro that will produce the desired output can be found below. It is useful to assign it a shortcut.

Sub fill_to_next()
   Dim nrow AS Long
   dim lastrow AS Long
   nowrow = ActiveCell.Row
   lastrow = Selection.End(xlDown).Row - 1
   If nowrow > lastrow Then
    ''
   Else
    col = ActiveCell.Column
    Selection.AutoFill Destination:= _ 
         Range(Cells(nowrow, col), _
               Cells(lastrow, col))
   End If
   Selection.End(xlDown).Select
End Sub

Leave a Reply