Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
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 ...
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 ...
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 ...

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.


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,

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


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


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


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 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.


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

HELP! I need to insert rows in a worksheet that has a formula on it. When I
use the Insert row command, my formulas get disrupted and all the rows after
the inserted row do not 'add-up'. How do I ask 'excel' to insert rows and
calculate my new row onward to integrate them within the formula?

PS. I am a TECHNOPHOBE! EASY TO FOLLOW JARGON WOULD BE APPRECIATED.

Cheers!

FRUSTRATED!



Is there a way to rewrite this into one statement so I don't have to repeat the same command 10 times? Thanks.

Sub Macro1()
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
Rows("6:6").Insert Shift:=xlDown
End Sub


Hi Guyz,

I was trying to write a macro to insert rows in between..

The sample data is mentioned below which starts from A2

AA AA AB AB CC CC DD EE

My aim is to insert a balnk row in between rows so that it can be differntiated. for example insert a row after AA and AB and so on which
will result as below

AA AA AB AB CC CC DD EE

to do so i tried using a simple formula =a3=a2 and copied the same till the last row and it resulted as below.

AA TRUE AA FALSE AB TRUE AB FALSE CC TRUE CC FALSE DD FALSE EE FALSE

So now I will have a criteria to insert row after every "FALSE" on my worksheet.

Was trying for the code but could not do anything....

Help please


I have a spreadsheet that I need to insert rows into. However I need to have the number of rows inserted to be set by a cell. I am not the best with vba but I can work with it. What i would prefer is to make a macro that allows me to perform the task.

I just realized this could use some clarification.

cell A1: 10
cell A2: 9
cell A3: 6

I would like to insert 10 rows between A1 and A2 and then 9 rows between what is now cell A11 and A12, etc.


I have recorded a macro to insert multiple columns throughout a spreadsheet and I got the error: Compile Error: Wrong number of agruments or invalid property assignment.

I deleted all the code for my selecting cells as I scrolled to the right as I was adding columns and it is down to only selecting the applicable columns and inserting the number of columns in to the right of the selected column. I do not see where there is an error in the code. I do not get a highlight or underlining of text to indicate the error.

Thanks for your help.
Rodney Jorgensen

Sub Columns()
'
' Columns Macro
' Inserting Blank Columns
'

'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight
Columns("BL:BL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BX:BX").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CD:CD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Columns("CN:CN").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CT:CT").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("DF:DF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EV:EV").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EZ:EZ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FD:FD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FH:FH").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FL:FL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FP:FP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FW:FW").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GK:GK").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GO:GO").Select
Selection.Insert Shift:=xlToRight
Columns("GS:GS").Select
Selection.Insert Shift:=xlToRight
Columns("GV:GV").Select
Selection.Insert Shift:=xlToRight
Columns("GY:GY").Select
Selection.Insert Shift:=xlToRight
Columns("HB:HB").Select
Selection.Insert Shift:=xlToRight
Columns("HF:HF").Select
Selection.Insert Shift:=xlToRight
Columns("HI:HI").Select
Selection.Insert Shift:=xlToRight
Columns("HL:HL").Select
Selection.Insert Shift:=xlToRight
Columns("HO:HO").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Range("HI1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("11:11").Select
End Sub




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


Guys,

How do I automatically insert rows given the identified quantity to insert and copy the contents of the previous item on the insert rows. Does that make any sense


a1 - abc
a2 - def
a3 - ghi

I need to insert 10rows below a1, and copy a1 across the inserted rows.
Now I have a ton of this case currently do it manuaaly, it might take me a lifetime to complete. Please help.

thanks,