Autonumbering

[Greg]

Any little scripts for auto-numbering rows on a
spreadsheet? The number of rows would be variable.

Thanks,

Greg

 

[Jim]

Greg:

Array Formulas work well for this.
Note: In each case, select the entire range,
enter the formula, then press Ctl + Alt + Enter

If you have a column or partial column named "Data":
Row(Indirect("1:"&Rows(Data)))

Or for 12 consecutive numbers in one column,
select any 12 consecutive cells in a column:
Row(Indirect("1:12"))

Or if you have a row or partial row named "Data2":
Column(Indirect("1:"&Rows(Data2)))

Or for 12 consecutive numbers in one row
select any 12 consecutive cells in a row:
Column(Indirect("1:12"))

Jim

 

[Shailesh Shah]

Hi Greg,

Try VBA Codes:

Sub FillSeries()
Dim rng As Range, stno As Long
Set rng = Selection ' or Range("a1:a100")
stno = 1 'autonumber starts from
rng(1) = stno
rng.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End Sub


Sub AutoNumber()

' fills upto maximum value = 65536 = cells.rows.count

Dim rng As Range, stno As Long
Set rng = Selection ' or Range("a1:a100")
stno = 1 'autonumber starts from

rng = Evaluate("row(" & stno & ":" & Cells.Rows.Count & ")")

'rng = Evaluate("row(" & stno & ":65536)")
End Sub





Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)