automaticaly assigning numbers to rows

[Adz]

Hi all,
This is probably a very easy question for the guru's here.

What i am trying to do is allocate a consecutive number to each row in a cell at the end of each row. this in itself is easy. The problem I have is if i need to delete or insert a row I need the consecutive numbers to change accordingly.

to give an idea of the layout, the rows are competition entries in various categories. some entries pull out and there are constantly entries being added to categories. The idea is that at the cutoff date entries will all have a unique entry number and we will have a total count of entries ( the total count is easy its just the changing unique number)

I hope i explained myself properly and I thank anyone who can help in advance

Cheers

 

[Adz]

ok.. turned off the stupid in my head lol.. cheers michael and everyone who helped, works perfect

 

[Adz]

I changed your script to make it do it the way I wanted it. works perfect cheers.
I do however have one more question, I need to do the same thing in the Judging score column(H) but it need to have conditions from column B

Unsure if I can explain myself properly but here goes..
column B has the category I.E. 1.1,1.2,2.1 etc.
I need column H to count the same value, skip blank cells then start count again when a new value is detected. and be able to remove and insert rows as with the other script.( both need to be running)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
94	666	2.1	Saddlers Creek Wines		Reserve	Riesling	2010	29		91
95	682	2.1	St Hallett Wines		Eden Valley	Riesling	2010	30		92
96
97	338	2.2	Ballast Stone Estate	Currency Creek	Water Ribbon	Gewrztraminer	2010	1		93
98	302	2.2	Ernest Hill		Maisie Evelyn	Gewrztraminer	2011	2		94
99		2.2	G. Patritti & Co Pty Ltd	Patritti	Blewitt Springs Estate	Gewrztraminer	2008	3		95
100
101	84	2.3	Burk Salter Wines	Burk Salter		Muscat Gordo Blanco	2009	1		96
102		2.3	De Bortoli Wines	Sacred Hill		Traminer/ Riesling	2010	2		97
103	119	2.3	Heafod Glen		Loose Goose	Viognier/ Chenin/ Verdelho	2011	3		98
104	221	2.3	Jacob's Creek Wines		Moscato White	Gordo/ Orange Muscat/ White Frontignac	2010	4		99
105
106	564	3.1	Australian Vintage LTD	Nepenthe		Chardonnay	2010	1		100
107	677	3.1	Blue Pyrenees Estate	Leydens Vale		Chardonnay	2008	2		101
entries proofread

Excel 2007


I'm sure I probably just need to use same script with a couple of bits added but this is fairly new to me and am having trouble working it out.

 

[Michael M]

Sorry Adz...had to go and play golf.....that was a waste of time !!!
If I understand you correctly, try

Sub adder2()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
s = 1
For r = 1 To lr
    If Range("B" & r).Value <> "" Then Range("H" & r).Value = s
    s = s + 1
    If Range("B" & r).Value = "" Then s = 1
Next r
End Sub

 

[Adz]

Hi Michael,
Bad luck with the golf mate.

The code you gave nearly does it just 2 little things.
1 It starts 1 in the heading row instead of row 2, the rest of the gaps in data are perfect.
2 when I delete a row it doesnt re adjust count (33,34,35 becomes 33,35)

Thanks heaps for all your help.

 

[Michael M]

Ok, a couple of changes

for r = 1
needs to be For r =2

and it needs to be a Worksheet change event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
s = 1
For r = 2 To lr
    If Range("B" & r).Value <> "" Then Range("H" & r).Value = s
    s = s + 1
    If Range("B" & r).Value = "" Then s = 1
Next r
End Sub

 

[Adz]

Ok,
starting number fixed ( I stared at that code for ages trying to work out which part indicated starting cell lol)
still dropping number when row deleted.

and your knowledge has inspired me to brush the dust off my VB for excel book i've had for years and never read and also to read every one of the 2.5 million posts on this forum lol.

 

[Adz]

also, not sure if it matters, it's in with code for other count as well

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Rng = Range(Cells(2, 10), Cells(Rows.Count, 10).End(xlUp)(2, 1))
Rng.Formula = "=IF(NOT(ISBLANK(B2)),COUNTA(B$2:B2),"""")"

Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
s = 1
For r = 2 To lr
If Range("B" & r).Value <> "" Then Range("H" & r).Value = s
s = s + 1
If Range("B" & r).Value = "" Then s = 1
Next r
End Sub

 

[Adz]

sorry for all the posts.
Ignore everything I just said... works perfect. Laptop was having a slow moment. closed and re opened and it's exactly what I need.

Again, many thanks.

 

[Michael M]

Glad you got it working....
Keep reading posts here to keep learning, we're all students ( well, me anyway)