Vilnis Vesma's Excel tip: self-extending tabulations

Sometimes you need to set up a spreadsheet with a tabulation that will grow as you add new rows of data from time to time (adding new meter readings each week, for example). Usually there will be formulae in columns of the spreadsheet, and you have to remember to copy the formulae down each time you add new input data.

This is a typical example -- a set of meter readings with formulae for calculating the meter advance:

The following macro, attached as worksheet code, will keep your formulae columns filled down as far as the bottom of the input data:


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim TargetCell As Range, N As Long
    
    On Error GoTo FormulaFillFail
   
    If (Not IsNumeric(Target.Value)) Or IsEmpty(Target) Then
        Exit Sub
    End If
   
    For Each TargetCell In Intersect(ActiveSheet.UsedRange, Target.EntireRow)
        If TargetCell.Value = "" Then
            For N = 1 To TargetCell.Row - 1
                If TargetCell.Offset(-N, 0).HasFormula Then
                    TargetCell.Offset(-N, 0).Copy Destination:=TargetCell.Offset(-N + 1, 0).Resize(N, 1)
                    Exit For
                End If
            Next N
        End If
    Next TargetCell

    Exit Sub
    
FormulaFillFail:

End Sub

The macro responds to any change in the worksheet (such as you entering a value in a cell). When a cell changes, the code first looks to see if the changed cell contains text or is empty: if so, it does nothing. However, if it finds a numeric value in the changed cell, it inspects every cell in that row which falls within the 'used range' of the worksheet.

It is looking for empty cells, because these may be candidates to have formulae copied into them. It decides which ones to populate by scanning up the column. If it encounters a formula in a cell above the target cell (the one it is inspecting), it replicates that formula down the column as far as the target cell. Then it moves on to inspect the next cell in the row, and so on.

The effect (in the illustrated example, which is available to download) is that as soon as you add a new reading, the correct formulae magically appear in the calculated column opposite your new data. You can have as many calculated columns as you wish, either side of your input-data columns, and it doesn't matter if the sheet is updated manually or by macros. Anything which extends the table will cause all formula columns to fill down to the row containing the new values.

V.V. 25 November, 2005