Rolling rows

[rasiel]

today i got stumped on what i thought would be a piece of cake.

i want to have a range of cells in a column numbered 1,2,3... i then
want to be able to insert a row, say between 2 and 3 and have the
numbers below it add one to the count as well as the correct number in
that cell auto fill in for the row just added. so that rather than have
1,2,blank,3 i would now get 1,2,3,4

in other words, recreate the functionality of the row number off to the
left.

i can make a range where if cell a1 is 1 then a2 could be a function of
a1+1. this sequence however does not auto-fill when i add a new row -
it just places a blank without rolling over the numbers.

i'm going nuts - i hope someone can give me a clever solution!

ras

 

[Don Guillett]

this assumes that you have a title row in row 1 and numbers in cell a2 and
cell a3. Now when you insert a row and/or add anything in col B the numbers
will be re-created automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
lr = Cells(Rows.Count, "b").End(xlUp).Row
Range(Cells(2, 1), Cells(3, 1)).AutoFill _
Range(Cells(2, 1), Cells(lr, 1))
End Sub