How to insert a new row and keep the formula? (Without using Tables!)

[ramilh]

I have been searching for a way to insert a new row with formula but could not find a solution. I don't want to use tables as it will completely mess up my formula. Please check the example workbook attached.

[Glenn Kennedy]

What do you mean? If you convert that to a Table, the refernces REMAIN as B2/C2 and the formula automatically appears in any inserted row.

[ramilh]

What do you mean? If you convert that to a Table, the refernces REMAIN as B2/C2 and the formula automatically appears in any inserted row.

I want the inserted rows to include formulae without using a table. I cannot use a table on my main workbook because it messes up thousands of rows of data

[Glenn Kennedy]

Show me how it messes it up.

[Pepe Le Mokko]

Try something like

Please Login or Register  to view this content.

[ramilh]

Try something like

Please Login or Register  to view this content.

It doesn't work. It still adds blank rows without formula.

[ramilh]

Show me how it messes it up.

I have insert row code as below (for a different purpose). When I insert table not only it messes up my whole formula system, it also doubles the size of the file (which is 15 mb as of now). It also doesnt let my below code to run.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, Durum As Range, r&
If Target.Count = 1 Then
lr = Cells(Rows.Count, "B").End(xlUp).Row
If Not Intersect(Target, Range("X4:X" & lr)) Is Nothing And LCase(Target) = "tamamlandı" Then
Set Durum = Range("X4:X" & lr).Find(what:="Tamamlandı", after:=Target, lookat:=xlWhole, MatchCase:=False)
If Not Durum Is Nothing Then
r = Durum.Row
If r <> Target.Row Then
Range("A" & Target.Row & ":x" & Target.Row).Cut
Range("A" & r & ":x" & r).Insert shift:=xlDown
Range("X" & r).Activate
End If
End If
End If
End If
End Sub

[Glenn Kennedy]

Still no sample sheet showing the problem.

[ramilh]

Still no sample sheet showing the problem.

I have uploaded the sample sheet. Please kindly check.

[Glenn Kennedy]

1. Move your shift times to an area ABOVE the data.

2. Demerge the headers aand move them to ONE row above the data body.

3. Increase the width of the header row.

4. Convert to Table.

5. Insert row (see purple cells).

6. Anything messed up? You may need to adjust the VBA a bit. I have no idea, as I do not use it. But the changes I made were ALL outside the data body.