Auto cell numbering

[SPLUCENA]

Hi Again,

Is there an easy way that I can auto number column A if there are entries in column B? Say I have 5 entries in column B , column A will auto number it from 1 to 5. I believe I saw this code before in this forum, I have been searching for it for about an hour now but to no avail. If anyone has it please help....


thanks,

splucena

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .Formula = "=ROW()"
    .Value = .Value
End With
End Sub

 

[Peter_SSs]

Hi Again,

Is there an easy way that I can auto number column A if there are entries in column B? Say I have 5 entries in column B , column A will auto number it from 1 to 5. I believe I saw this code before in this forum, I have been searching for it for about an hour now but to no avail. If anyone has it please help....


thanks,

splucena

Maybe you are looking for a code solution, but manually (assuming no gaps in the column B data) you could ...

1. Enter the first two numbers (1 and 2)

2. Select those two cells and double click the Fill Handle (the little black square at the bottom right of the selection)

 

[Yard]

Or this in A1 and copied down. Depends what you want.

=IF(LEN(B1)=0,0,SUMPRODUCT(--(LEN(B$1:B1)>0)))

 

[SPLUCENA]

Hi Friends,

Thanks for the quick reply and formulas. VoG's code is what suites my project. Thanks again to all of you!!!!!!

splucena

 

[SPLUCENA]

Hi Vog,

I forgot to mention that my data entry on column B starts at row 13 and ends at row 25 (this actually is an invoice). I tried to modify your code to start at row 13 but what it is giving me is number 13 itself and not 1. Please assist.


thanks,

splucena

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A13:A" & LR)
    .Formula = "=ROW()-12"
    .Value = .Value
End With
End Sub

 

[Peter_SSs]

Or maybe this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> AutoNumber()<br>    <SPAN style="color:#00007F">With</SPAN> Range("B13", Range("B" & Rows.Count).End(xlUp)).Offset(, -1)<br>        .Resize(1).Value = 1<br>        .DataSeries Type:=xlLinear, Step:=1, Trend:=<SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>