Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Insert 2 Rows Every Nth Row

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I would like a macro to insert 2 (or more) rows every 8th row.

Thanks.

B.

View Answers     

Similar Excel Tutorials

How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Quickly Resize Multiple Columns or Rows at Once in Excel
How to quickly resize multiple columns and rows at once in Excel.  This avoids having to individually resize rows ...
Insert Check Mark in Excel - 3 Ways - Incl. VBA and UDF
This tutorial goes beyond other simple check-mark tutorials. Here, I'll show you 3 methods to insert a check-mark i ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
Delete All Rows that Contain a Specific Value in Excel
Quickly find all rows in Excel that contain a certain value and then delete those rows. This is a simple technique ...

Helpful Excel Macros

Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the

Similar Topics







I have a spreadsheet with 20+ sheets and alot of formulas. There are a total of 210 ranges that I want to insert rows into. They are spread over the different sheets. Some of the sheets only have 1 range to insert rows into an others have up to 25 ranges to insert rows into. It is taking a very long time for the code to insert the rows into each range. So, I opened the worksheet, disabled macros and tried to insert the rows manually. It is taking a long time to insert the rows just into one section. It takes up to 5 seconds for each range. When I go into another spreadsheet I have with lots of formulas and insert rows there, it is quicker. Does anyone have any idea why it's taking so long to insert rows?




Hello everyone !!

It is my first post and I must say that it is a great forum with lot of useful information.

I am working on a spreasheet which is password protected. It has two section one is protected other is open.

Users are allowed to insert rows in protected area however they cannot copy and insert due to protection. So I have worked and developed a macro which allows them to do so. The only question I have is how to allow them to insert the copied cell to where ever they want.

Ideally I would like them to highlight a row and it insert the copied row there or may be input the row number (like Row 24) below which to insert the copied Row.

I have the below code at the moment to copy and insert and it only insert in the next blank row. I am new to VB and I think the below code can also be improved a bit.

Thanks for any help/suggestion.



Please Login or Register  to view this content.



I am trying to insert several formulas into blank rows to calculate data in rows above the blank rows. Example: I have data in columns (the amount of rows varies and are seperated by 3 blank rows) and need to put a formula in the first blank row after each varying series of data that calculates the cells above all the way to the blank row above.

Blank Row
data
data
data
INSERT FORMULA (to sum above 3 rows)
Blank Row
Blank Row
data
data
data
data
data
data
Insert Formula (to sum above 6 rows)
Blank Row
Blank Row
data
data
Insert Formula (to sum above 2 rows)
Blank Row
Blank Row
data
etc.....

I have been trying different types of code and I am getting formulas in all three blank rows and summing everthing except the top data row. Any help or suggestions woul dbe greatly appreciated.

Thanks
Luke


Below is the code in which I would like to know how to simplify. I would like to do a loop but I don't know if that will work. I have about 300 rows that this macro would be inserting rows. This is only a small portion of it.


Quote:

Code:

Sub Input_Lines_1()
'

'
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("25:25").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("32:32").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("39:39").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("46:46").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("53:53").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("60:60").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("67:67").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub







Okay, I have had to figure this out 3x from scratch and my personal.xlsb isn't saving.

Since I really need this code to finish my program and parse my data I'm appealing you those who are quicker than me.

I am looking for code the will take the variable count (number of rows to insert) and insert that number of blank rows BELOW the activecell.

I believe the 3x times I succeeded I used a combination of Activecell, offset, insert, etc.

Any help you can provide is GREATLY appreciated.


Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete. Can anyone assist.


I have a spreadsheet that has data in rows 2-21

There is then more data in multiple rows below 22. What I would like to do is copy rows 2-21 and then insert them between each one of the rows below 22

Any ideas how I can do this through a macro? It is a huge spreadsheet and I cannot do this by hand. Thanks in advance for your help.


Hi,

I'm trying to create a macro that will insert rows based on the number of items in a row, and then transpose that information into the new rows.

Essentially I'm trying to take my horizontally stores data and make it vertical.

I've attached an example excel sheet to show what I'm trying to do.

Thanks for any help.


Hello,
I posted this in the wrong forum (General Excel Discussion & Other Questions)
I need to insert a row every 19th row after A2
I tried using this code:
Code:

Sub addblankrowevery19()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 20 Step -19
Rows(i).Insert
Next i
End Sub


But it didn't work.
Can someone guide me on how to get it to work, or a better solution?
Thanks,
Xrull


Is there any simple way to insert blank rows between two rows with different numbers!

Eg:

column a:

23
34
37
56


i want to insert blank rows between 23 and 34 rows which is around 10 blank rows , and again 2 blank rows between 34 and 37 and so on ..

Another situation is sometime i have two same value

column a:

23
34
34
37
56


In this, i dont want any blank rows between 34 and 34

How this can be done !

Many thanks in advance !


I need to insert a clock counter for 28,8000 rows at 3 second intervals. I have data in an existing worksheet. I have to accomplish the following Insert a column then prompt for start time.. for ex: 17:00:00 then increment the rows by 3 seconds upto 28,800 rows. 17:00:00 17:00:03 17:00:06 so on and so forth upto 16:59:57 Can any one help me with this?

Hi there,

I am wanting to insert a whole new row between each row in the existing data (say A2:A10) - A10 being the last cell used in the column

On this new row I am wanting to insert some formulas such as in cell A3 =IF(A2>5,Yes,No).

I am then wanting this to loop through for all the cells in existing data range.

I have looked through the existing posts on this and am still a litttle stuck. I have been able to insert the spacing rows but not not insert the formula beneath.

Im not new to excel, but new to VBA. Any help would be kindly appreciated.


I have a client that wants to protect formulas on a spreadsheet and allow users to insert duplicate rows but only with existing formulas where they exist. In other words, I need to insert a row above row 7, and A7-D7 are data entry cells, but E7-G7 are formulas and need to be copied to the new row.

What's happening is that he has a very complex spreadsheet with multiple tabs linked to each other with very complex formulas, but he needs to allow users to insert and add lines. He has trained them to copy and insert rows, but also has to allow them to unprotect the entire sheet in order to copy and insert rows with set formulas. What is happening is that when they unprotect to do so, a formula can be accidentally erased without anyone realizing it.

I seemed to think that it is just a training or proofing issue since multiple users have to be trusted to copy and paste the formulas correctly, but wanted to find out if there is a way to keep the sheet protected and insert duplicate rows as needed.

I am using excel 2003. Thanks!


Hi all

I am using the subtotal function to insert page breaks.

I would like some code which will search for the page breaks in my worksheet and automatically insert 2 blank rows above each Page Break.

Might this be possible?

Many thanks


I have about 20 rows of data and I'm trying to insert a row of data before line 6. I highlight cell 6, click Insert /Rows and then I get an error, "To prevent the possible loss of data, Microsoft Excel cannot shift nonblank cells off the work sheet."
It goes on with some more garbage, but it's already obvious that Microsoft doesn't know what it's talking about. How do I get this insert to work?
Thanks!


Hi,

I am using excel for bookkeeping. Sometimes when I have added several rows of information, I discover that I missed some info and I need to insert a row a few rows above (everything goes by date, and it has to be in the right order). When I do this, all my formulas shift down of course, but this is not convenient.

Example- I have the following 2 rows of data in 3 columns:
A1 | B1 | =A1*B1
A2 | B2 | =A2*B2

When I insert a row in between I get:

A1 | B1 | =A1*B1

A2 | B2 | =A2*B2

But I want:

A1 | B1 | =A1*B1

A3 | B3 | =A3*B3

so I can insert the row of '2' values, like such:

A1 | B1 | =A1*B1
A2 | B2 | =A2*B2
A3 | B3 | =A3*B3

Part 2 of the problem: the first sheet is connected to a second sheet with the formula =sheet1!A1, etc. Essentially Sheet 2 is just a copy of certain values of sheet 1 (the input sheet) which it then uses to calculate some statistics. I do no manual input on sheet 2.

When I insert a row on sheet 1, it does not insert a row on sheet 2, but it does change all of the formulas by +1, so now =sheet1!A2 changes to =sheet1!A3.

Is there any way to lock the references on sheet 2 so that for example the value in sheet2A2 ALWAYS equals the value in sheet1A2, no matter what happens on sheet 1?
I tried =sheet1!$A$2, but that also becomes =sheet1!$A$3 when I insert a row on sheet 1.


So far I've just been correcting it manually every time by retyping the formulas and re-entering all the data, and then dragging down the series. Besides being tedious, this also significantly increases the margin of error. There's got to be a better way!!!

Any ideas?

~Charles


Could you guide me please....

I need to insert 5 blank rows repeatedly between every existing data rows
for approximately 300 rows.

If I go about doing the repeat short-cut "Control+Y", it just repeats
inserting ONE row only between the consecutive data row.

Is there some command, which helps me highlight all the rows & allows me to
insert 5 blank rows between every consecutive existing data row?

Thanks in advance for your kind advice.



I've got several spreadsheets where I have a list of dates and balances in Columns A & B, however column A doesn't contain all the dates. I need a macro where I can insert additional rows between cell where there is a value in Column A. In addition to this I also need these inserted rows to be populated with date in Column A (Target Data V1 - so that column A runs sequentially from start to finish with no omissions or gaps), and the balance value in Column B to the populate any blanks with the value from the previous row (Target Data V2).

I can figure out how to insert rows, but not the correct number based on the differences in the value between 2 cells.

Anyone offer any suggestions?


i need to insert a blank row every 3 rows.

I found the following code from a previous thread for inserting a blank row every 5 rows. Can the following code be tweaked to make it work for inserting a blank row every 3 rows.

http://www.excelforum.com/excel-misc...ry-5-rows.html

Sub AAA()
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = 6 To LastRow Step 6
Rows(RowNdx).Insert
Next RowNdx
End Sub


I'm afraid that I know the answer to this but I thought I'd ask anyway ...

Is it possible to insert columns for only a certain section of the
spreadsheet, i.e., to affect only a certain range of rows?

Over the years, I've created a spreadsheet with about 300 rows (and with a
6,000-line macro) and now realize that I need about 10 extra columns ... but
only in a certain part of the spreadsheet (rows 100 to 150).

If I insert columns, the ENTIRE spreadsheet (from row 1 to 65536) is going
to be affected (and all of the address references in my macro will have to be
redefined) and then I'll have to cut-and-paste all of the fields from rows 1
to 99 and 151 to 300 back over to their original columns and then all my
row/column references are going to get screwed up and it's going to become a
major hassle.

So, I thought that there might be a trick to inserting columns in specific
rows without affecting any of the other rows. (Wishful thinking, I know.)

Thanks. Dan



I have this protected worksheet which is stopping people from clicking on the cells they don't need to access, hense protecting my work from being used by others in my workplace, but there is an issue I have faced and I don't know how to get around it.

What I want people to be able to do is insert a Row, but since the worksheet is protected they are unable to do this, If I tick "Select Locked Cells" & "Insert Rows" then yes people can insert rows but it means they can click on the cells I don't want them to click on, but can't edit them. I also have "Select Unlocked Cells" ticked so they can put in data that is required.

In a short I want to be able to: Lock Cells so others can't click on the ones they will never use Allow Users to Insert Rows Protect worksheet
I really hope you all understood that, So what are my options?

Thanks
JamieLee2k


Hi, first I want to thank you for your help and this website.

My question is how do I insert multiple rows after every change in column?

I am able to insert one row with either of the following; however, I can't figure out to insert 4 rows:

Code:

Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select
Do Until Selection.Row = Number_of_rows + 2
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 2
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

OR 

'Insert row after each change in FUND CODE'
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Cells(lRow, "B") <> Cells(lRow - 1, "B") Then Rows(lRow).EntireRow.Insert
Next lRow



Thank you for you help,
Mike


New to programming for excel. Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.

For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.

Not sure how/where to enter the script either. I'm fine with in-cell formulas, and using the record-macro tool, but have little experience writing and running scripts in excel so as detailed as possible would be greatly appreciated.

Thanks so much


Hi,

I have a sheet of around 5000 rows, I would like a macro to do some manual work for me.

Is it possible for a macro to insert a row if a "1" is present in column I. If possible I would like the row to be inserted above the 1.

Thanks


I have a list of parts. I'd need to copy/paste/insert the same 4 rows under each part #, until end of list. Simply, it would be like this (assume my source to be copied from is rows 1-4.
starting in 1st part #, paste insert rows under existing row. go down 5 rows which will now be under the next new part number and do again, until list is complete.