Fill ascending numbers

[Dreamer]

Hi,

col A Col B
A


B


C

--> Anyone can suggest how to fill the numbes in col A like this...

Col A Col B
1 A
1
1
2 B
2
3 C
3


~ Thank you ~ :help

[austenr]

How many rows do you want to fill in each column or do you want to fill all of the rows?

[Bob Phillips]

col A Col B
A
B
C
--> Anyone can suggest how to fill the numbes in col A like this...

Col A Col B
1 A
1
1
2 B
2
3 C
3



Dim iLastRow As Long
Dim i As Long
Dim idx As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value <> "" Then
idx = idx + 1
Cells(i, "B").Value = Cells(i, "A").Value
End If
Cells(i, "A").Value = idx
Next i

[mdmackillop]

Hi Dreamer,
It's not totally clear from the layout if the letters are to be shifted to Coumn B or not. If everything is to be kept in column A, this variation to XLD's code will do that.



Dim iLastRow As Long
Dim i As Long
Dim idx As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value <> "" Then
idx = idx + 1
Cells(i, "A").Value = idx & Cells(i, "A").Value
Else
Cells(i, "A").Value = idx
End If
Next i

[Dreamer]

Hello, just one column to be filled, but a total of 30000 rows....

Let me explain it more clearly, sorri for unclear information.. :bow:
My desired format is..

col A Col B
1 -----A
1
1
2 -----B
2

Originally, one column exists with Letters A,B.
Ideally, I want to insert a new row (A) with outputs as shown above.

[mdmackillop]

To insert an additional column, try:


Dim iLastRow As Long
Dim i As Long
Dim idx As Long
Columns("A:A").Insert Shift:=xlToRight
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value <> "" Then idx = idx + 1
Cells(i, "A").Value = idx
Next i