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

Hi,

Post you current formula

Mike

"jfcby" wrote:

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,

My current formula:

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

Thank you for your help,
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

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
---------------
Jacob Skaria


"jfcby" wrote:

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