Email:      Pass:    Pass?
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


Free Excel Forum

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 Video Tutorials

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 attached the simple example worksheet I am using.

My goal is to have a macro that will always insert a new line between the specified range of rows which I gave a defined name of " COLUMNS".

I can get a macro to insert new rows, but if I add rows above where I am working, it no longer inserts rows into the right areas. You can see the macro I have in there.

If I insert rows above where my data is, the macro continues to insert the line in the same space instead of adjusting for inserted rows. How do I avoid this?

Hi,

I want to know that how i can insert rows after specifice selection

e.g i have two values in drop down menu in b4. Insert-1,Insert-2
if insert-1 selected from drop down then there is no need to insert but if insert-2 selected then three rows after (b10).

I think its done by macros can any one assist me to do so?

Best Regards,
Rob

and each three rows contain vales in b10,b11,b12,SKU,PO,TPPO respectively


I have thousands of rows and most of the time I need to insert certain # of rows in between rows. Lets say between Rows 1114 and 1115 I need to insert 34 new rows. Instead of counting 34 rows down the lane then right click Insert, I would just like to select row 1115 and run a macro that will prompt a message box asking "Number of New Rows" and I will enter 34, click OK and that will insert new 34 rows right betwen rows 1114 and 1115. Can you pleaes help? Thanks in advance.


Hello,

I want to restrict users from inserting rows, if they want to insert a row I want them to be asked the row number at which they would like to insert the row.

The insert effectively needs to be an insert of three copied rows and keep the same formating.

For example, the insert should always be the same as the three consecutive rows starting from row 19 in the attached spreadsheet.

I don't even know where to begin.


Also...I want to print the logo in the top left corner in the same scale but not see it on the page. I've tried customer header but it makes it massive and I've tried print titles and then hiding rows 1,2 & 3. this doesn't work either.

Thanks in Anticipation
Steven


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 takes about 8 minutes to insert 10 rows the first times. Then I try to insert 10 more rows and it takes about 20 minutes. When I try to insert the 5th set of 10 rows, it takes about 2 hours! The code that's being executed, inserted the rows in all 210 ranges and then it loops through the 210 ranges and copies the formulas down to the newly inserted rows. Before we do all of this, we turn the calculation off. Does anyone have any idea why it's taking so long to insert rows?


Hi,

I want to insert blanks rows above rows that have the number 1 inserted in column C.

I about 60,000 rows in all, doing it manually takes forever because I have to keep waiting for Excel to push the rows down every time I insert a new row.

Is there an efficient way to grab all the rows with a 1 in column C and insert rows in one swoop? If not, I will settle for any way other than manually.

Thank you.


Hi guys, i'm doing this macro that will insert rows for those I want to. Basically it's those rows after the subtotals.
My file looks somethin like this & I would have a formula which reads "Insert Rows" so I can filter them, select visible cells & insert:
Mth ACC Remarks TOTAL
Jul-10 A 50
Jul-10 A 30
A Total 80
Jul-10 B Insert row 25
B Total 25
Jul-10 C 75
Jul-10 C 10
C Total 85

The VBA for the filtering & deleting:
Selection.AutoFilter Field:=4, Criteria1:="Insert Row"
Rows("4:10").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Insert Shift:=xlDown

The error that appeared:
Run-time error '1004':
Insert method of Range class failed

Appreciate if u guys can explain to me what I can do differently with the recording of the macro rather than just provide the VBA solution. cos I am very unfamiliar with vba.
Thks!


Hi,

I would like to create a macro that inserts a row at the point of insertion eg row 25, at the same time insert a row at row 35 and also row 45. I would like to be able to use this macro to insert a row in a different place as well so not just at 25. So if the point of row insertion was row 28, insert a row at 38 and 48. As well I would like to be able to copy the formulas down from each of the three rows when the new rows are inserted into the spreadsheet. Is it possible to do this so the new rows can be inserted anywhere but still have the 2nd and third rows inserted 10 rows below. Using excel 2007 by the way.
Thanks for any assistance


I am using the following to insert a blank row after every 10 rows but would like it to also put NEW in A1.

Code:

Sub doit()
    Dim i As Long
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 3 Step -10
            Cells(i, "A").EntireRow.Insert
        Next i
End Sub


could this also be changed to insert blank row after 10 rows if there are 20 or more rows in the sheet and if there are less than 20 to divide the number of rows in half and insert the blank row?

Marty


Can Excel Run a Macro to Format Sheet?

I am trying to accomplish the following:

1. Macro runs and Insert 10 blank rows after each item row. EXCEPTION: if rows are identical on Column A do not insert. Insert - only after non-duplicates. There are times where there 3-4 rows that are part of the same group.

2. Insert a bottom border line to separate each group.

See attached for sample.


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 all, I need a simple script to walk down a column of data (A) and insert 2 rows when the code finds a change in the values in column A (and then loop until blank value in column A). For example, if column A contains value 'Red' in rows 1-3, then 'Blue' in rows 4-10, the macro would insert 2 rows between rows 3 and 4. Thanks, David


I have 2 issues...they are probably easy but I am a relative novice...

First...Is there a way to expidite the insert rows feature? The way this spreadsheet is set up...I have to insert hundreds of rows and was wondering if there was a way to insert multiple rows at a time.

Second...I have the following in a bunch of cells...1-10...is there an easy way to change it to 1,2,3,4,5,6,7,8,9,10...?

Thanks...


Hi guys, i couldn't get an answer from another forum for this. i'm doing this macro that will insert rows for those I want to. Basically it's those rows after the subtotals.
My file is in the attachment & I would have a formula which reads "Insert Rows" so I can filter them, select visible cells & insert:

The VBA for the filtering & deleting:
Code:

Selection.AutoFilter Field:=4, Criteria1:="Insert Row"
Rows("4:10").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Insert Shift:=xlDown


The error that appeared:
Run-time error '1004':
Insert method of Range class failed


Hello,

I have the below code for adding 6 rows after each date change in my spreadsheet, but it doesn't add rows after the last row of data. Can someone please help?

' *** Insert 6 blank rows after each change in Transmit Date *** '

'Declare Variables'

Dim r As Long

'Perform insert Rows through sheet'

For r = Range("A" & Rows.Count).End(xlUp).Row To 6 Step -1
If Cells(r, "A") Cells(r - 1, "A") Then
Rows(r).Resize(6).Insert
End If
Next r


Thank you,
Liz




I need a simple macro to insert a row from row 6 onwards every 2 rows down as fas as row 2000. So it would be like me manually going to row 6, highlighting row 6, right clicking and hitting insert row, then going to row 8 and repeating...

Hello Forum,
Is there another way to write the code to add multiple rows instead of the recorded macro ? ;
Code:

Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert


so the code is written in one line to add 8 rows.
The "8" would be variable, so I might require elsewhere 10 rows or whatever the case may be.

Thanks.


Please add something more so that I can be able to insert more than one rows with the option to insert these before or after any selected cell

Sub VBAddRows()

InputBox ("How many rows would you like to add?")
Selection.EntireRow.Insert
End Sub

Thanking you in anticipation.


Hi All,

I have an excel (2003) workbook that contains nearly 70 (no. keeps on changing) tabs. In my main tab named as "New" there is header in 1st 2 rows. I need a macro which copies these 2 rows and insert these rows in all other tabs.

PS: I want to insert (not paste) these 2 rows as the data in all other sheets start from row 1 onwards.

I hope I have cleared myself. Please revert if you have any questions for me.

Thanks a lot in advance for all your help.




Hello all,

I am trying to create a macro to find a group of rows, copy and them and insert them above the existing rows.

In column A, I need to find the cell containing the text "Insert new projects". Then I want it to copy all of the rows below that until it gets to a cell that says "Summary". Then I want it to insert/paste all of those rows (the ones between "Insert..." and "Summary") above the "Insert..." row exactly as they are.

This could include 10 rows or 30, just depending on the sheet it's run on, but it will always be the rows between (but not including) "Insert New Projects" and "Summary".

Any help would be very much appreciated.

Thanks,
RL

How do I insert rows which include predetermined text and equations (example sums and means). Is it possible to insert these rows in a multiple selection (example if I select row 3 and 15, the rows should insert underneath these two rows.

See my other post:http://www.mrexcel.com/forum/showthread.php?t=318619


I have recorded a macro to insert six rows. I'd like to take this one step further and have the user enter the number of rows they want to insert. Right now the data is entered in A10:F33. Can someone help me with this? Many thanks!


Hi.

I'm new to macro and vba so please go easy on me.

I've a macro that I use to insert rows so I can copy down formula. My last two rows on the sheet contain footer rows so those need to stay. I just need to insert enough rows in between the first record row and just before the last two footer rows. Can't leave any blank rows.

I've a count formula on another sheet in the same workbook to count new records.

How do I go about referencing the result in the count field so the macro would insert the needed rows?

Would appreciate any help. Please don't go too technical on me. I'm an accountant.

This is how my macro looks right now. I would have to go in and manually put in the last row number -- in this case "117".

'
Rows("4:117").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("3:117").Select
Selection.FillDown
End Sub


I would love to simplify this code. It is extremely long and I have to have 3 macros to complete the full need for what I am doing. It is very repetative. I am inserting 6 rows between the information that I have on my data sheet. However, I now have the need for a 7th row to be added and do not want to have to go back through and manually edit every number if I can help it.

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
    Rows("74:74").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("81:81").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("88:88").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("95:95").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("102:102").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("109:109").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("116:116").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("123:123").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


Thank you so very much.


Hi. I'm new on here but would really value any help you can give. I have a spreadsheet containing around 500 lines of unique data in rows. I now need to insert 20 rows of common data after each of the 500 rows. I have a macro that will insert 20 blank rows but need help to populate the blank rows with the 20 rows of data. The macro I have is:

Public Sub My_Insert()

Const bBLOCK As Byte = 20

Dim lRows As Long
Dim bLoop As Byte

For lRows = Range("A65536").End(xlUp).Row - 1 To 1 Step -1

If WorksheetFunction.CountA(Rows(lRows)) 0 Then
For bLoop = 1 To bBLOCK
Rows(lRows + 1).EntireRow.Insert (xlShiftDown)
Next
End If
Next lRows

End Sub

Can anyone help. Thanks in advance.

Big C