ID numbers
set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup))
i = 0
for each cell in rng
i = i + 1
cell.Value = i
Next
or you could use the fill method
Sub Renumber()
Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Cells(1, 1).Value = 1
Rng.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End Sub
--
Regards,
Tom Ogilvy
"Gareth"
wrote in message
...
I have a sheet containg rows of data, column A has an ID
number. For example - 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.
Users amend the ID number and click a button which sorts
the sheet in ID ascending order. The user may want to
amalgamate 1 and 7 so he will change the 1 to a 7 and
click the sort button:
Before clicking
7, 2, 3, 4, 5, 6, 7, 8, 9, 10
After clicking
2, 3, 4, 5, 6, 7, 7, 8, 9, 10
What I want to add on to the end of the sort macro is a
bit of code that will renumber the ID's so that they make
sense, that is, start at 1 and don't have any gaps.
Is this possible?
Thanks in advance.
Gareth
|