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 Every Other Row ???

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

Hi,

If I have a column of data, can I after selecting this data insert a blank row every other row of that column (to insert a blank row between each 2 rows), is there a function can do that or I need a macro to do it?

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

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
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
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
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
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.

Similar Topics







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


When I try to manually insert a column by highlighting it and then right-clicking and selecting insert, I get an error from Excel that "Excel can not insert a new column, because of non-blank cells off the worksheet". But when I manually setup a test workbook and insert headers and with data, clearly it is not blank, is allows me to insert a column, what is wrong with Excel ?

Or what am I doing worng ? I mean, this is annoying, I copy that sheet to a new workbook with the same data and try to insert the column and no error, it works ? and it is not protected, is this another Excel bug ? even if I select that column and delete all the data, I still can't insert a new column, I get that same annoying stupid error!

Excel 2003.

Looking for a VBA solution, basically trying to insert a column infront of AG which is not blank, headers start at A6, data at A7.

Thanks!


Hi,
I have a file with 18,000 rows of data, the data is separated into blocks of data by blank rows so eg.

row 1-4 has data t be summed, row 5 is blank, rows 6-15 has data to be summed, row 16 is blank, row 17-35 has data to be summed etc.

The sum will need to cover the range eg column D:CD (months going out 10 years+). The data values will be blank or zero or populated with a number.

Can I get a macro to look down eg column A, where it is blank, insert a sum function to pick up the range above, then loop to the next blank row and sum etc down to the bottom ?

Each block of data will have a unique identifer on each row eg.

column B for rows 1-4 could have identifer 002PM, rows 6-15 could have 002PN, rows 17-35 could have 002SJ etc.

Any help would be greatly appreciated, (don't want to have to do this manually although it would keep me busy for the rest of the month !).

thanks
Steve


Hi Folks,

I'm trying to come up with a macro to insert a blank row when data in column B changes.
Column B contains Fund Codes and I want to insert a blank row between each of the funds for easier reading. I've searched past posts but was unable to modify anything that I saw to work for me.

As always, any help is GREATLY appreciated. Thanks.


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 need to insert some blank rows in a column. Column A contains 60,000 rows of data. I need to write macro that will insert a blank row every X rows. I say X because I am not sure how many breaks I will need yet...Thanks

James


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


Basically what I'm trying to do is this.



I want to insert a page break after every different occurence in a column. For instance, say column B has data as follows.



1

1

2

2

3

3



I would like to insert a page break after each occurance of a different number as follows.



1

1

-

2

2

-

3

3



Then what I would like to do is batch insert row sheets so that between every page break is a number of cells divisible by 8 for printing reasons. So what it would look like is this



1

1

blank

blank

blank

blank

blank

blank

-

2

2

blank

blank

blank

blank

blank

blank

-

3

3

blank

blank

blank

blank

blank

blank



If you could help me somehow batch ANY of this process, I would really appreciate it. This job is rather tedious done by hand so this would really save me some time.



Thanks!


Hi all,

I would like to create a macro to automatically insert a blank row between data when the value in column D changes.
Fe. the 1st 100 rows column D has value 'G', but in row 101 the value in column D changes to 'H' for the next 120 rows ..

What formula do I need to write in my macro in order for excel to automatically insert a blank row every time the value in column D changes.

Thank you.

Jeroen


I have a dataset in excel with heading in first row and repetitive data in groups next rows.
The first column contains month1 month2 month3 month4 total and total

I want to insert a blank row after last total of each group and column headings after that
so I want to insert a blank row and column headings after last total row of first group and before the row starting with month1 column.

How can I write a macro to insert a blank row and column heading in vba?

Thanks in advance

Blyzzard


I have a spreadsheet which returns data from a text file, I have also written a counting function which returns how many times the entry in column A occurs and puts the number on the right. The sheet is sorted by column A low to high, so say for example A14:A18 contained 190062 and A13 and A19 were different my results column would show (blank,blank,blank,blank,5). So far so good, but now my boss wants a blank line inserting in between each change in column A and I am having real difficulties with this. I showed him subtotals but he didn't like it, said all he wanted was a space in between the rows to make it neater. I tried writing a macro to insert a row using the changes in my counting function as a guide but it inserts the rows at the same place every time, which is no good. I basically need something along the lines of 'insert row at n if AnAn-1'. Can anyone help?


I have a spread sheet of data and I want to insert a row every 2 rows until it reaches a blank space in the 'A' column...... is that possible to create some sort of insert row loop?


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




Hello,

Please see attached workbook where need to put up a button to Insert new rows. On clicking this button it should go to last NON-blank row (i.e. before 'End of List' is mentioned) and insert a blank row below with formatting similar to the previous one.

E.g. Goto Sr. No. 25 if that contains data in column B and C then insert blank row below with fomatting similar to Sr. No. 25.

Hope I'm clear with my problem. Thanks in advance.

Regards,
Novice




I have a worksheet looks like this:

Column A
row 4: 0
row 5: 1
row 6: 0
row 7: 3
row 8: 2
row 9: 0
row 10: 1
............

What is the code to insert blank rows by value of each row in column A above that row (for example, if the value is 1, then insert one blank row above it, if the value is 3, then insert 3 blank rows above the cell with value 3), the outcome looks like this:

0
(one blank row)
1
0
(3 blank rows)
3
(2 blank rows)
2
0
(one blank row)
1

Thanks
Tom


I need to insert a blank row after every row containing data (to facilitate
reading and adding survey data by hand) in an Excel spreadsheet. Is there any
way to do so automatically by selecting the data then doing some menu option
or keystroke? I can add a blank row with a macro but that is only a bit less
laborious than having to add each blank row by hand. Thank you for your help.
Margaret



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 !

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 am new to macros but i believe this is what i need to do to save time on separating totaling a spreadsheet. My current project that involves 20k+ rows of data from invoices. Column A has my invoice # in it and there may be 2-5 rows per invoice after which I need to insert two blank rows for each invoice. Additionally I have to sum Column F , J:N and P:Q on the first new line I insert, the second row just stays blank. I have attached a sample to show what I am trying to accomplish even if there is a way to just insert the row that will save alot of time.

Thanks for the help

I have many records. They have many rows with the same date in Column D.
I would like to add a macro that would insert a blank row after each date change in Column D.
I would then like to add a macro to convert it back, deleting all blank rows.

Is this possible say with a checkbox? If checked add blank row, if not, delete blank rows.

Thank You,
Michael


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


I've had some help with this so far but it isn't working as expected.

The following is intended to insert a blank row anytime it encounters a change in the entire cell content in column B.

It should only make one pass and ignore an already blank cell in B.

Sub insert_row()
Columns(1).Insert
With Range("b3", Range("b" & Rows.Count).end(xlUp)).Offset(,-1)
.Formula = "=if(b2b3,1,"""")"
.Value = .Value
On Error Resume Next
.SpecialCells(2,1).EntireRow.Insert
End With
Columns(1).Delete
End Sub


Hi, I'm trying to insert a bit of code into my existing click button macro to insert a row based on a condition being met and then insert a formula into the resulting blank cell.

Range to look at = L1:L1000

Condition = "=IF(LEFT(R[-1]C,13)=""ORCSW FRM ***"",""insert row below here"",""go to next"")"

Code to insert into blank cell created in column L = "=""ORCSW HDW PNL ""&TEXT(R[-1]C[-3]*0.2,""00000.00"")&RIGHT(R[-1]C,4)"


Assuming this is easy enough to solve, I then want one final line of code to save the text in order in range L1:L1000 to a file called "labour00.txt", but only cells that have a do not equal "" or are not blank.

Thank you in advance for any help you can give.


Hi

I am complete novice and have been asked to create an excel spread sheet. Can someone please supply me with a macro that I can use to insert a blank row after each change in data name
e.g.

Site A
Site A
Site B
Site B
Site C
Site C

to become

Site A
Site A
(Blank Row)
Site B
Site B
(Blank Row)
Site C
Site C

I have found the follwoing macro but this creates a blank row after each line not when each time when the name changes as the amount of rows per name varies.

Sub Insert_Blank_Rows()

'Select last row in worksheet.
Selection.End(xlDown).Select

Do Until ActiveCell.Row = 1
'Insert blank row.
ActiveCell.EntireRow.Insert shift:=xlDown
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop

End Sub

Thank you

Bug


Hi to all!

I have 3000 rows in a sheet. I'm using following code to insert blank row becasue i need to create space between two rows.

Quote:

Sub InsRows()
Dim LR As Long, i As Long, j As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
Rows(i).Insert
Next i
End Sub

Now the problem is that i need also to insert a blank column between two columns.My columns are "A:AC".

i need expert advice where i will add extra line of code that will do both work like insert row and also column.

My english is not good. so i beg your pardon.
regards