Insert blank rows

pontiff

Board Regular
Joined
Jun 11, 2009
Messages
143
Office Version
  1. 2016
Hi,
I have some vba which gets me to the first cell in the row I need to begin which ends with

Target.Offset(2, -2).Select

I then need to insert 7 blank rows below this row (essentially creating the gap i require).

As always, any help much appreciated.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Here is one way:
VBA Code:
    Target.Offset(2, -2).Select
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 7).EntireRow.Insert Shift:=xlDown
 
Upvote 0
Here is one way:
VBA Code:
    Target.Offset(2, -2).Select
    Rows(ActiveCell.Row + 1 & ":" & ActiveCell.Row + 7).EntireRow.Insert Shift:=xlDown
Thanks for this! Does it have to be the entire row or could just the first 8 cells of the row be inserted? (apologies I should have said that there is some other data to the right of where I want to insert)
 
Upvote 0
You had originally said to insert "blank rows", which is why I did that.
Just remove the ".EntireRow" part if you want to insert cells instead of rows.
 
Upvote 0
You had originally said to insert "blank rows", which is why I did that.
Just remove the ".EntireRow" part if you want to insert cells instead of rows.
Fantastic, thanks so much!
 
Upvote 0
You are welcome..

Did that work for you? I forgot that I was using "Rows", so I think that despite my initial thoughts, it may requrie a little more editing, like this:
VBA Code:
    Target.Offset(2, -2).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(7, 0)).Insert Shift:=xlDown

Or, you can do it all in one line like this:
VBA Code:
    Range(Target.Offset(3, -2), Target.Offset(9, -2)).Insert Shift:=xlDown
 
Last edited:
Upvote 0
Solution
It is more likely my poor explanations! Basically I have a sheet with a number of identical tables.I have a macro which will copy and paste a new table under the others. Unfortunately there is a "Total" cell just under the last table which gets pasted over so my thought was to insert a gap between the bottom of the last table and the "total" cell so when I run the macro there is space to paste it into. Hope that makes some sense.
 
Upvote 0
So, do any of my solutions do what you need them to do?
If not, maybe post an example of before/after to show us what the data looks like, and what you want it to look like after this is done.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
So, do any of my solutions do what you need them to do?
If not, maybe post an example of before/after to show us what the data looks like, and what you want it to look like after this is done.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Yes the singe line of vba you posted in your latest reply is just what I need. Thank you! And thanks for the other info too
 
Upvote 0
Excellent!
Glad to hear that it worked out for you.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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