ID numbers
perhaps I didn't make it clear exactly what I'm after.
start ID's:
1,2,3,4,5,6,7,8,9,10
user changes 1 to a 4 and sorts, gets this:
2,3,4,4,5,6,7,8,9,10
I want to resort the ID's to end up with:
1,2,3,3,4,5,6,7,8,9
Or
start ID's:
1,2,3,4,5,6,7,8,9,10
user changes 4 to 9 and sorts, gets this:
1,2,3,5,6,7,8,9,9,10
I want to resort the ID's to end up with:
1,2,3,4,5,6,7,8,9,9
Hope you can help.
-----Original Message-----
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
.
|