Adding lines formula?

excelNewbie22

New Member
Joined
Aug 4, 2021
Messages
27
Office Version
  1. 2013
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

excelNewbie22

New Member
Joined
Aug 4, 2021
Messages
27
Office Version
  1. 2013
Platform
  1. Windows
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: 3

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 4, 2021
Messages
27
Office Version
  1. 2013
Platform
  1. Windows
peter, now i got it, this isn't what i wanted, tnx for the effort
 

excelNewbie22

New Member
Joined
Aug 4, 2021
Messages
27
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,986
Office Version
  1. 365
Platform
  1. Windows
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 & ");"
 

Forum statistics

Threads
1,141,284
Messages
5,705,508
Members
421,399
Latest member
hjweiss00

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
Top