Here is a different approach for a macro solution (change the sheet name in
the With statement to the worksheet name you want to apply this
functionality to)...

Sub FillRangeE10DownToRowNumberInA1()
Dim CounterCell As Range
With Worksheets("Sheet3")
Set CounterCell = .Range("A1")
With .Range("E10")
.Resize(.End(xlDown).Row - .Row + 1).Clear
.Value = 1
If CounterCell.Value 1 Then
.Offset(1).Value = 2
If CounterCell.Value 2 Then
.Resize(2).AutoFill .Resize(CounterCell.Value)
End If
End If
End With
End With
End Sub

Note that the first line after the second With statement clears from E10
down to the last filled cell in that column. This allows you to change A1 to
a lower number than the current last row and the excess currently filled in
numbers will be deleted. In other words, if A1 contains 100 and E10:E109 is
currently filled in, changing A1 to 20 will clear E10:E109 and then fill in
E1:E29 as you wanted. If you won't need this functionality, you can remove
that one statement.

Also note that if you change your mind and want to number from a different
cell, say K5, then just change the "E10" to "K5" in the second With
statement and the code will work fine with the new cell reference. You can
also change the cell you use to keep the counter in from A1 to any other
cell, say for example B2, by changing the "A1" to "B2" in the Set statement.

--
Rick (MVP - Excel)


"bijan" wrote in message
...
Hi all,
I need a vba code for autonumber cells from E10 to the a variable number
that I insert in A1, for example if A1=20 it fill E10:E29 with 1-20
Thanks in advance
Bijan