ID numbers

[Gareth]

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

 

[Tom Ogilvy]

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"

 

[pikus]

lrow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count

For x = 1 To lrow
ActiveSheet.Cells(x, 1).Value = x
Next x

How's that? - Piku

 

[Gareth]

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.

 

[Tom Ogilvy]

perhaps I didn't make it clear exactly what I'm after.
No you didn't.

Try this:

Sub Renumber1()
Dim lastVal As Long
Dim rng As Range, i As Long
lastVal = -1
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
i = 0
For Each cell In rng
If cell.Value <> lastVal Then _
i = i + 1
lastVal = cell.Value
cell.Value = i
Next
End Sub