Modifying existing code

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
peace,


Code:
Sub insert()
Dim start As Long
Dim rows As Long
start = Range("E1")
rows = Range("F1")
Cells(start, 1).Resize(rows).EntireRow.insert
End Sub


how to modify this code so that the same functionality that E1 and F1 has can also be duplicated to E2, F2, E3 and F3. In other words how to add more cells than just E1 and F1 to it. . .
will appreciate help!!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
peace,


Code:
Sub insert()
Dim start As Long
Dim rows As Long
start = Range("E1")
rows = Range("F1")
Cells(start, 1).Resize(rows).EntireRow.insert
End Sub


how to modify this code so that the same functionality that E1 and F1 has can also be duplicated to E2, F2, E3 and F3. In other words how to add more cells than just E1 and F1 to it. . .
will appreciate help!!
Hi,

You can easily add a loop to do this. But the end result may vary depending on the ordering of the "start" values. Do you want these sorted into any particular order (largest to smallest in E could be a good idea) or just take them as they come?

Say you have 3 values down each of columns E and F
Code:
Sub insert()
Dim start As Long
Dim rows As Long
Dim j As Long
For j = 1 To 3
    start = Range("E" & j)
    rows = Range("F" & j)
    Cells(start, 1).Resize(rows, 4).insert
Next j
End Sub
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
Do you want these sorted into any particular order (largest to smallest in E could be a good idea) or just take them as they come?

I want them to take them as they come in a loop ... thank you for code I'll let you know how it works out for me. :)
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
the code works but some times the row inserted is off set by -1. Maybe the blanks has something to do with it.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,956
Office Version
  1. 2019
Platform
  1. Windows
nevermind the formula in cell E1 is giving me some trouble...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,576
Members
413,996
Latest member
mabelO

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