Hopefully an easy one for you on this sunny friday afternoon (well it is in london anyway).
I have a workbook that is filled out by users. As a result of them keep inserting rows and knocking my formulas out of whack i have added an insert row button for them. The problem i have is that i have a rownumber counter in column A. when they insert a new row i want the column to autofill from 1 down to the last row. Currently the code is set to go down 150 rows but as i add each new row it still only goes down to 150 rows.
I hope that makes sense, here is the code im currently running for this procedure. The problem i have is of course in the last row.
'messagebox asking you to select the row number where you would like to insert the row
'adds 39 to the number entered to allow for hidden rows
rownum = InputBox("Please Enter The Row Number You Would Like To Add A New Row To") + 39
If rownum < 41 Then
MsgBox ("You Cannot Insert A Row Here Please Try Another Row")
'inserts the row and shifts everything down 1 row
'autofills the row count at the bottom so that shows 1-150 with no interruptions
Selection.AutoFill Destination:=Range("A40:A189"), Type:=xlFillSeries
Thanks for the response. Had a little trouble with that code as the variable kept coming back with row 39 (not 190 or so) however you made me think logically about the problem and i realised the easiest way to do it was your way.