ID numbers
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 |
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 |
ID numbers
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 -- Message posted from http://www.ExcelForum.com |
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 . |
ID numbers
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 -- Regards, Tom Ogilvy "Gareth" wrote in message ... 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 . |
All times are GMT +1. The time now is 06:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com