Incrementing numbers in a column

[Rodman]

Can someone help me? I am looking for a formula or macro to increment
numbers in a column such that when a number is entered in an empty cell,
the remaining numbers (in the column) are incremented by 1. In the examples
below, the Before example represents a spreadsheet with numbers in certain
cells in Column A.

The After example represents placing a number in an empty cell (A3) and the
remaining numbers in the column are incremented by 1.

Before
|COL A
1|1
2|
3|
4|2
5|
6|3
7|
8|4
9|
10|5


After
|COL A
1|1
2|
3|2
4|3
5|
6|4
7|
8|5
9|
10|6

(I would like this formula/macro to be in effect for all of Column A.)

Thanks In Advance!

 

[JMay]

In your Sheet Module paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
If Application.Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(Target.Row + 1, Target.Column), Cells(lrow, 1))
For Each cell In Rng
If cell <> "" Then
cell.Value = cell.Value + 1
End If
Next cell
Application.EnableEvents = True
End Sub