Excel: set default formula for each cell in column

autocompletemicrosoft excelworksheet-function

I have a column with the following formula in every cell that calculates the difference in kilometers between the two cells to the left of each cell:

=IF(AND(INDIRECT(ADDRESS(ROW(),COLUMN()-2))<>"", INDIRECT(ADDRESS(ROW(), COLUMN()-1))<>""), INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2)), "")

empty cell with no formula when I insert a new row

My problem is that every time I insert a new row in between existing data, all the cells are empty in that row and I need to manually put this code into the empty cell.

Is it possible for me to set every cell in a column (excluding the header) to a specific formula even when I insert a new row?

I have tried to set the column as a table, and it was successful in autocompleting the cells whenever I inserted a new row, however, I have other columns that I also want to autocomplete and Excel doesn't let me insert a new row if I make more than one column a table.

Best Answer

Here is a small demo that you can adapt to your actual schema. In the demo:

the column of interest is column B
if more than one row is inserted, nothing will happen
if a row below the data area is inserted, nothing happens
if a row is deleted, nothing happens
if a row in the middle of the data is inserted, the cell in column B will be filled with either the formula above or the formula below
if there is no formula above or below the new cell, nothing happens

Insert the following sub in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, r1 As Range, r2 As Range

    Set r = Target
    If r.Rows.Count > 1 Then Exit Sub
    If r.Columns.Count <> Cells.Columns.Count Then Exit Sub
    If r.Row = 1 Then Exit Sub

    Set r1 = Intersect(r, Columns(2))
    If r1.Value <> "" Then Exit Sub

    If r1.Offset(-1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(-1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

    If r1.Offset(1, 0).HasFormula Then
        Application.EnableEvents = False
            r1.Offset(1, 0).Copy r1
        Application.EnableEvents = True
        Exit Sub
    End If

End Sub

To install the macro

Open Excel -> Alt+F11 -> Insert -> Module -> paste the following code -> Ctrl+S -> and select 'Excel Macro-Enabled Workbook (*.xlsm)' from the drop-down list

Sub ToManyColumns()
    Dim firstCellRow As Long
    firstCellRow = 1              'change this if you don't want to start at A1
    Dim firstCellColumn As Long
    firstCellColumn = 1           'change this if you don't want to start at A1
    
    Application.ScreenUpdating = False
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Dim column As Long
    column = firstCellColumn
    Dim startIndex As Long
    Dim endIndex As Long
    Dim lastRow As Long
    lastRow = firstCellRow
    
    Do While True
        'find the range to copy
        startIndex = ActiveCell.row
        Do While ActiveCell.Value <> ""
            endIndex = ActiveCell.row
            ActiveCell.Offset(1).Activate
        Loop
        
        lastRow = ActiveCell.row
        
        Range(Cells(startIndex, firstCellColumn), Cells(endIndex, firstCellColumn)).Select
        Selection.Copy
        Cells(firstCellRow, column).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
        'get back to last rowIndex
        Cells(lastRow, firstCellColumn).Activate
        ActiveCell.Offset(1).Activate
        
        If ActiveCell.Value = "" Then Exit Do
        
        column = column + 1
    Loop
    
    'cleanUp -------------------------------------------
    Dim deleteFrom As Long
    Dim deleteTo As Long
    deleteTo = ActiveCell.row
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    Do While ActiveCell.Value <> ""
        ActiveCell.Offset(1).Activate
    Loop
    deleteFrom = ActiveCell.row
    
    Range(Cells(deleteFrom, firstCellColumn), Cells(deleteTo, firstCellColumn)).Select
    Selection.ClearContents
    
    ActiveSheet.Cells(firstCellRow, firstCellColumn).Activate
    'cleanUp -------------------------------------------
    
    Application.ScreenUpdating = True
End Sub

To run the macro:

Make sure that you're in the worksheet you want to be! And click anywhere into the worksheet! (Because this macro runs in the Activated Worksheet)
Alt+F11 -> Click anywhere into the code -> press F5

You can also create a button in the worksheet and assign the macro to it - it's more user-friendly and you don't have to check that you're in the right worksheet.

Notes

If you don't want the macro to start at cell A1 (for example: start at different column), then change the numbers on 3rd and 5th lines.

For this purpose it's better to preprocess the data than use macros...

Best Answer

Related Solutions

Excel – How to set Function “formula” for whole column in Excel 2007

Excel – Split long column into multiple columns by empty cell

To install the macro

To run the macro:

Notes

Related Question