Adding lines formula?

[excelNewbie22]

How to Add a certain line after each existing line with numbering?
like let's say this is the lines:

6, 7, 8, 9, 10, 11
12, 13, 14, 15, 16, 17
18, 19, 20, 21, 22, 23

and i want to add after each line the next phrase with a number, like this:
6, 7, 8, 9, 10, 11
New line (#1);
12, 13, 14, 15, 16, 17
New line (#2);
18, 19, 20, 21, 22, 23
New line (#3);

and so on...
can it be done in excel?
​

 

[Joe4]

Here is one way:

Sub MyInsertRows()

    Dim lr As Long
    Dim i As Long
    Dim ctr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through data
    For i = 1 To lr
        ctr = ctr + 1
        Rows(i * 2).Insert
        Cells(i * 2, "A") = ctr
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[Peter_SSs]

Is this what you mean?

21 08 24.xlsm
	A	B	C	D	E	F
1	6	7	8	9	10	11
2	12	13	14	15	16	17
3	18	19	20	21	22	23
4	24	25	26	27	28	29
New lines

Cell Formulas
Range	Formula
A2:F4	A2	=$F1+COLUMNS($A:A)

 

[excelNewbie22]

for joe:
maybe i run it wrong? i hit alt+f11 , then insert module, save it, back to excel and alt+f8 and this error appears

for peter:
i didn't understand what the formula is doing

 

[Joe4]

That error seems to suggest that you have two different macros with the exact same name.
Did you accidentally copy/paste this macro twice?

 

[excelNewbie22]

peter, now i got it, this isn't what i wanted, tnx for the effort

 

[excelNewbie22]

That error seems to suggest that you have two different macros with the exact same name.
Did you accidentally copy/paste this macro twice?

u r right!
it worked!
but... how do i combine the phrase in code?
the code is:
New line (#1);
and just the number changing...

 

[Joe4]

u r right!
it worked!
but... how do i combine the phrase in code?
the code is:
New line (#1);
and just the number changing...

If you want it to say exactly "New line (#1);", then change this line of code:

        Cells(i * 2, "A") = ctr

to

        Cells(i * 2, "A") = "New line (#" & ctr & ");"

 

[Peter_SSs]

peter, now i got it, this isn't what i wanted, tnx for the effort

No problem. I will leave it to Joe.

 

[excelNewbie22]

thanks joe!
all worked out!