Occasional Row Miscount Causing Overwriting of Header Row

[mharper90]

I use the below setup to fill formulas on an excel data worksheet. The first 5 rows are full of buttons and other reference cells, and row 6 is the header row for the data. Occasionally, and without a clear pattern, this macro will miscount the rows and start putting formulas in row 6 instead of row 7, which overwrites the header row.

Any ideas on how to fix or prevent this?

Sub fillFormula3()

Dim Lr As Long
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")

Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row

ws1.Range("E7:E" & Lr).Formula = "=IF(D7="""",D$4,D7)"
ws1.Range("F7:F" & Lr).Formula = "=IF(G7="""",G$4,G7)"
ws1.Range("M7:M" & Lr).Formula = "=SUM(I7:L7)"
ws1.Range("N7:N" & Lr).Formula = "=IF(M7="""","""",(H7-M7))"

ws1.Range("A7:B" & Lr).Interior.Color = RGB(248, 203, 176) 'light pink
ws1.Range("M7:N" & Lr).Interior.Color = RGB(248, 203, 176) 'light pink

With ws1.Range("A7:N" & Lr).Borders.LineStyle = xlContinuous
.ColorIndex = 11
End With

With ws1
With Application.ErrorCheckingOptions
.BackgroundChecking = False
.EvaluateToError = False
.InconsistentFormula = False
End With
End With

End Sub

 

[Norie]

Which columns have data/formulas?

 

[steve the fish]

Ill guess its when ws1 column A just has the header so this line:

Lr = ws1.Range("A" & Rows.Count).End(xlUp).Row

produces 6 then this:

ws1.Range("E7:E" & Lr).Formula = "=IF(D7="""",D$4,D7)"

becomes

ws1.Range("E7:E[COLOR=#ff0000]6[/COLOR]").Formula = "=IF(D7="""",D$4,D7)"

which is valid and will put the formula in E6 and E7 hence overwriting the header in row 6. Do a test on Lr to see if it is greater than 6 before placing the formulas.

 

[mharper90]

BINGO! Now that you mention the real problem, I am able to recall what events trigger it. This macro is set to run after a delete macro, and when deleting the last data row on the page (leaving just the header), it is doing exactly what you described. I will add a test for Lr and I think that'll solve it perfectly. Thanks!