Adding lines formula?

excelNewbie22

Active Member
Joined
Aug 4, 2021
Messages
403
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,120
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

Active Member
Joined
Aug 4, 2021
Messages
403
Office Version
  1. 365
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: 6

Joe4

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

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

excelNewbie22

Active Member
Joined
Aug 4, 2021
Messages
403
Office Version
  1. 365
Platform
  1. Windows
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
66,120
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,181,598
Messages
5,930,800
Members
436,761
Latest member
mintwaxed

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