Auto Increment Rows in between numbers
 

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.
--
D

Auto Increment Rows in between numbers
 

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

"D" wrote:

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.
--
D

Works Excellent
 

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

"Jim Thomlinson" wrote:

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

"D" wrote:

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.
--
D