Auto Increment formula

[jfcby]

Hello,

The formula below is in A1. I have 200 rows 30 columns of data. How do
I get the formula in A1 to auto increment when data is add below the
last cell with data?

Thank you for your help
jfcby

 

[Mike H]

Hi,

Post you current formula

Mike

 

[jfcby]

Mike,

My current formula:

=IF(SUBTOTAL(9,A4:A152),IF(ISDATE(A4),"",SUBTOTAL(9,A4:A152)), "")

Thank you for your help,
jfcby

 

[Jacob Skaria]

I am not sure whether this suits your requirement..Copy this formula from A2
to down

=$A$1+Row()

If this post helps click Yes

 

[jfcby]

Hi,

Is there a better solution than the macro below.

<CODE BEGIN>

Sub InsertRow3()
'Add row / Insert Row, Copy/Paste Row contents, Clear Row contents
'To increment formula when adding data below last row with data _
insert row above last row with data then copy last row data _
& paste data in inserted row
'Insert row above last row with data (ActiveCell)
ActiveCell.Offset(0, 0).EntireRow.Insert
'Copy last row with data contents (Below ActiveCell)
Rows(ActiveCell.Offset(1, 0).Row & ":" & _
ActiveCell.Offset(1, 0).Row).Copy
'Paste last row with data contents (ActiveCell)
ActiveCell.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
'Clear last row with data
Rows(ActiveCell.Offset(1, 0).Row & ":" & _
ActiveCell.Offset(1, 0).Row).ClearContents
'Select Last Row
ActiveCell.Offset(1, 0).Select
End Sub

<CODE END>

Thanks for your help,
jfcby