Excel: Automatic numbering of rows (without duplication) 3

[MeGustaXL]

I have a Range named “Database” which starts as $A$9:$M$10. I’ve attached the following code to a button on the sheet, which appends a new line to the bottom of the range and numbers it sequentially.

Public Sub BottomLine()
Application.ScreenUpdating = False
Dim iRowCount As Integer
With Range("Database")
'   Count the number of existing rows, resize the database range,
'   then assign values to the RefNo Cell.
iRowCount = .Rows.Count + 1
.Resize(iRowCount).Name = "Database"
RefNo = iRowCount - 1
End With
'   Number the new row as RefNo.
Range("A65536").End(xlUp).Offset(1, 0).Value = RefNo – 1
End Sub

This works fine when starting from scratch, ie with no data in Database, clicking the button will number cell A10 as “1”, A11 as “2”, A12 as “3”and so on. The problem arises if I delete a row, then add a new one, I end up with duplicate row numbers

For example, if I’ve got rows numbered 1 to 5, then delete row 3, the next new row is numbered 5. Any ideas how to avoid this duplication would be gratefully received


Chris

Varium et mutabile semper Excel

 

[dyarwood]

Is the index you want equal to the row number in Excel? If so can you not use a Row address command?

 

[MeGustaXL]

Hi, dyarwood, and thanks for your quick response!

I've tried something like you suggest, by using the Row Number minus 9:

Public Sub BottomLine()
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Row - 9
ActiveCell.Offset(0, 1).Select
End Sub

but that suffers from the same illness; if I've got rows numbered 1 to 5, delete row 3, then add a new row, that new row is numbered 5

I'm thinking maybe I need to keep a record of the "Largest Row Number Ever Allocated" and count from there. That way, I won't even duplicate previously used numbers. How to do that?.....Ah, there's the bit that eludes me!


Chris

Varium et mutabile semper Excel

 

[dyarwood]

You might need an Onsheetupdate or something similar. If you run your code, then delete a row, then run it again what happens?

 

[dyarwood]

Alternatively could you have the following formula in column A

=IF(B1<>&quot;&quot;,ROW(),&quot;&quot

if there is something in cell B1 then the row has a number in it otherwise it doesn't. This would mean that there is no gaps in column B

dyarwood

 

[MeGustaXL]

Same thing, unless I just delete the cell contents and not the entire row. That's obviously because (real) rows 10 to 14 are (pretend) numbered 1 to 5. By deleting (pretend) row 3, I've actually deleted (real) row 12, moving (real) rows 13 and 14 up.

If you see what I mean!


Chris

Varium et mutabile semper Excel

 

[dyarwood]

This should work.

Public Sub Bottom1Line()
Range(&quot;A65536&quot.End(xlUp).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = &quot;=Row() - 9&quot;
ActiveCell.Offset(0, 1).Select
End Sub


Might need to put it in a loop though
dyarwood

 

[combo]

If your database accepts formulas - use (changing when deleting rows):
Range(&quot;A65536&quot.End(xlUp).Offset(1, 0).FormulaR1C1 = &quot;=ROW(RC)-9&quot;

If uou need fixed values, you can keep current counter in a named range, increase it by 1 when record added.

combo

 

[MeGustaXL]

Great stuff dyarwood and combo, but neither solution (which are spookily similar ) gives me a unique reference number. Once a row (of data) has been numbered, I want it to retain that number for the rest of its life, regardless of its position up or down the list.
If I number from 1 to 5 then delete 3, I still want the others to be numbered 1,2,4 and 5. If I then sort by column B, say, the numbers might end up as 4,1,5,2 but still referring to the same data.

I'm thinking along the lines of:

Go to next blank row in column A
Give this cell a number, one greater than the highest number already in column A.
Copy>Paste_Special>Values

This way, if I've had numbers 1 to 12, but deleted 2,6,8,9 and 10, then sorted by column B which has put 12 in 2nd place. the next line to be added will be numbered 13, but will be on row 17....can this be done?



Chris

Varium et mutabile semper Excel

 

[TonyJollans]

Hi relmanz2000,

I was going to suggest incrementing from the row above, but have just seen your last post, so try this.

Instead of

RefNo = iRowCount - 1

.. use ..

RefNo = WorksheetFunction.Large(.Columns(1), 1)

.. and then instead of ..

Range("A65536").End(xlUp).Offset(1, 0).Value = RefNo - 1

.. use ..

Range("A65536").End(xlUp).Offset(1, 0).Value = RefNo

+

 1

Its one shortcoming, which most methods will have, is that if you delete the row with the highest number, that number will get reused. if that is a problem you are going to have to store the highest used number somewhere else.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[dyarwood]

You could use

Sub maxi()
Dim x As Integer
Dim y As Integer

Range(&quot;C1&quot.FormulaR1C1 = &quot;=MAX(C[-2])&quot;
x = Range(&quot;C1&quot.Value
y = x + 1
Range(&quot;A65536&quot.End(xlUp).Offset(1, 0).Select
ActiveCell.Value = y
Columns(&quot;A:A&quot.Select
Selection.Copy
Range(&quot;A1&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

This will look for the highest value in column A and put it in cell C1 then uses that value +1 into the new insert. Also copies and paste specials the values only. The delete should work to. Let me know

dyarwood

 

[combo]

You can keep number to use in a named cell (&quot;RefNo&quot, for empty database equal 1.
In this case the last lines of code under button can look like:

[tt]Range(&quot;A65536&quot.End(xlUp).Offset(1, 0).Value = Range(&quot;RefNo&quot
Range(&quot;RefNo&quot=Range(&quot;RefNo&quot+1
End Sub[tt]

combo

 

[MeGustaXL]

Gee, Thanks you Guys!

Tony, I tried yours first, and it's nice and slick, providing there's at least 1 number in column A. Errors if not, but not a big problem, so = *.

dyarwood: Yours works very nicely, too, so have a glittery thing! I'm using yours for the time being, having added

Application.ScreenUpdating = False and Application.CutCopyMode = False

where appropriate.

combo: I haven't tried yours yet, but it looks like a neater version of dyarwood's - I'll get back to you when I've tried it out.

Once again thanks for all the help folks, I wouldn't be the office Excel Guru without you!


Chris

Varium et mutabile semper Excel