Insert 2 Rows Every Nth Row

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

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.

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

1 1
2 3
3 2
4
5 6



Hi I have a spreadsheet with a total on the last row, I want a macro to insert 5 blank rows above the total row (listed at row 5 in the above example) at the bottom.

the trouble is of course as soon as you insert 5 rows, then next time I need to run this, the macro will need to detect which is the total row again before inserting another 5 rows.

any help/advice would be appreciated.

hello all,

i need to write a macro that can insert cells a1:d1 for a specific number of times in the next empty cell.

example: (commas are the delimiters/column separators)

1, 2 , 3 , 4


a , b , c , d
e , f , g , h
i , j , k , l
m , n , o , p

the macro needs to find the next row that is empty (="") after M-P
and then insert 1,2,3,4 for the next 5 rows or 7 rows or 10 rows(user-defined, maybe an inputbox?)

so a1:d1 has to come in after a5:d5, and populate the next ___ Rows.

Any ideas?

Hi,

I have been looking around on the forum and have been unable to find anything which fits the VBA requirements i am looking for... I am hoping someone can kindly help with a macro

I have a spreadsheet which in column G has a number value in each most cells.

I am trying to get a macro to loop through each cell in the column and insert the number of rows per the cell value minus one i.e if cell value is 10 it would insert 9 rows, if it was 5 it would insert 4 rows.

This would insert entire rows below the cell with the value

Not all cells in this column have a value so have entered End in the last cell. So it can be ended on the loop once End have been reached.

Alot of the cells have 1 or 0.5, these cells should not insert any rows.

I would be really greatful if anyone can help .

Thanks
Dan