Auto Increment Rows in between numbers

[Guest]

Hello.
I have a spreadsheet that has a list of numbers on the left side (Column A)
and I need to know how to create a macro that when I hit a button a row will
insert in between the list of numbers and automatically fill in the number
that goes there. If that makes sense. So if I have a list of 1-20 and I
insert a row between 15 and 16, it will put 16 in that cell and put 21 at the
end of the list. Thanks so much for you help.

 

[Guest]

Try this code...

Public Sub InsertARow()
Dim rngCurrent As Range
Dim rngToFill As Range

Set rngCurrent = Range("A" & ActiveCell.Row)
Set rngToFill = rngCurrent.End(xlDown)

rngCurrent.EntireRow.Insert
Set rngToFill = Range(rngCurrent.Offset(-2, 0), rngToFill)

rngCurrent.Offset(-2, 0).AutoFill rngToFill, xlFillSeries

End Sub

HTH

 

[Guest]

Thank you, works like a charm! That is awewsome.

Try this code...

Public Sub InsertARow()
Dim rngCurrent As Range
Dim rngToFill As Range

Set rngCurrent = Range("A" & ActiveCell.Row)
Set rngToFill = rngCurrent.End(xlDown)

rngCurrent.EntireRow.Insert
Set rngToFill = Range(rngCurrent.Offset(-2, 0), rngToFill)

rngCurrent.Offset(-2, 0).AutoFill rngToFill, xlFillSeries

End Sub

HTH