Reference error

[Lisa928]

I have a spreadsheet that has the following information in column A. A simple calculation that I use as a line id.

A1 = 1
A2 = A1+1
A3 = A2+1
A4 = A3+1
A5 = A4+1

The problem is that this file is updated frequently and sometimes rows need to be removed. Is there any way through a formula (or VBA if not) that I can stop this from happening?

 

[paliman]

I made this with the macro recorder. May be it needs some adjustments

Sub Macro2()

Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[Mark W.]

Use this instead...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1
2	2
3	3
4	4
5	5
6
Sheet2

 

[paliman]

The former code won't stop it from hapening, but it will update your formulas after deleting rows.

 

[Lisa928]

The OFFSET works great. Thanks so much!