Adding lines formula?

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Here is one way:
VBA Code:
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
 
Upvote 0
Solution
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
 

Attachments

  • 1629806992831.png
    1629806992831.png
    3.2 KB · Views: 8
Upvote 0
That error seems to suggest that you have two different macros with the exact same name.
Did you accidentally copy/paste this macro twice?
 
Upvote 0
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...
 
Upvote 0
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:
VBA Code:
        Cells(i * 2, "A") = ctr
to
VBA Code:
        Cells(i * 2, "A") = "New line (#" & ctr & ");"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top