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:


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.

  • This can easily be done with an Event macro.........is this approach okay?? – Gary's Student Mar 10 '18 at 11:56
  • Your question looks complicated because you have shown only one column. Edit the screen shot and include columns to the left of existing one. You may get some non VBA solution also. – Rajesh S Mar 10 '18 at 16:22
  • Thomas when you insert Row/Rows in between definitely you find empty cells/row/rows since the formula is the part of the row so that you got empty cell for the formula also. It's like when you delete data from all source cells/rows/columns then formula has nothing left to calculate. – Rajesh S Mar 11 '18 at 11:39

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

  1. the column of interest is column B
  2. if more than one row is inserted, nothing will happen
  3. if a row below the data area is inserted, nothing happens
  4. if a row is deleted, nothing happens
  5. 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
  6. 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
|improve this answer|||||

I was going to just post this video, but evidently, I have to type at least 30 characters.

How about "Here is an easy way to do it." Darn, that's only 29.

For anyone interested, the "Insert Picture" icon won't accept a .mov, but there's a pretty neat website that will convert video files to animated GIFs.

enter image description here

|improve this answer|||||
  • I've up voted this simple but a handy solution. – Rajesh S Mar 11 '18 at 11:29
  • How did you create the .mov file ? – Gary's Student Mar 11 '18 at 11:46
  • Hi, @Gary's. Screen recording is a (little known) feature in Quicktime.I really hadn't used it before, but had wanted to find out how to do it. It was pretty easy to use. – Bandersnatch Mar 11 '18 at 13:39
  • @Bandersnatch Thanks!......................very cool! – Gary's Student Mar 11 '18 at 13:40
  • Thanks! The only issue I had was when you start recording, you're in QuickTime, so the QT menu bar is showing. I navigated to Excel, and then edited out the first few seconds afterwards. Using Trim... In QT. :-) – Bandersnatch Mar 11 '18 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.