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



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 Tutorials

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 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 ...
Insert and Manage Page Breaks in Excel
How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and confusing but this tutori ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...

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


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


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?


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 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 need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N.
If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range? Any help greatly appreciated.

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


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




I am looking for help to create a macro that would take a column of continuous data and insert 2 blank rows in between each cell of data then copy the data cell and paste it into the 2 blank cells. Effectively creating 3 cells of the same data. I have 3769 lines of data which is why id like to make this automated to perform this task. I have been trying for 2 days to no avail to accomplish this.

Apologies for the improper title description when I posted this yesterday as I am new to the forum.

I am selecting 20 rows, and I want to insert a blank row in between each of them. How can I do this?

Thanks,

P

(using excel 2000)


Hello,

Newbie here with my first post. I'm just a beginner with Excel VBA and have managed to create a few simple macros and edit a few macros I've gotten off the web. I'm trying to do something and and can't seem to quite find / figure out how to edit a macro to suit my purpose. Here's the scenario.

Worksheet with data in range A3:K58. Note that the range will always start on row 3, but it is possible that the range might increase from time to time with additional rows. I want to do 2 things.

First, I want to go thru the range and remove any blank rows.

Second, I want to go thru the range from top to bottom and insert a single blank row conditionally. The condition is as follows: Column K is a "check sum" sort of column. It will have a non-zero value at the top, and at some point (row) the value in this column will change to zero and be zero after that for the rest of the range. (An enhancement would be to verify that this is indeed the case.) All I need to do is to insert a blank row above the first row that has a zero in column K.

Thanks in advance. I'm using Excel 2003 on Windows XP.

GTS