Adding new rows in between rows...

  • Thread starter Thread starter MarshaMarsha
  • Start date Start date
M

MarshaMarsha

I need to add a blank row in between each row and the length of the document
is seven pages; so, is there a way I can do this without having to add them
individually?? If you can help me with this........thanks for saving my
sanity!!!
 
Sub insertrow()
lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2
MsgBox lastRow
For i = 1 To lastRow
i = i + 1
Range("A" & i).EntireRow.Insert
Next
End Sub
 
Thank you Mike!!

Mike said:
Sub insertrow()
lastRow = Range("A" & Rows.Count).End(xlUp).Row * 2
MsgBox lastRow
For i = 1 To lastRow
i = i + 1
Range("A" & i).EntireRow.Insert
Next
End Sub
Click to expand...
 
Hi

Try this macro

Sub InsertRows()
Dim r As Integer
Dim TargetRange As Range
Set TargetRange = Range("A1", Range("A1").End(xlDown))
rCount = TargetRange.Rows.Count
r = 1
For r = rCount To 2 Step -1
Rows(r).Insert
Next
End Sub

Regards,

Per
 
A simple macro will do this for you but the conditions in the macro will
depend on the contents of your 7 pages of data. For example, the following
macro will insert a blank row between each of your existing rows and will
stop as soon as it encounters a blank cell in column A (the blank cell is
the signal to end the procedure or it will continue looping and creating new
rows until it reaches row 65526.

Sub InsertRows()
Range("A2").Select 'Starting location for the
procedure
Do While ActiveCell <> "" 'Do the actions until you reach a
blank cell
Selection.Insert Shift:=xlDown 'Insert a blank row
ActiveCell.Offset(2, 0).Select 'Move to next row to be tested
Loop 'Start again from the
Do While line
End Sub

If you have data in every row in column A for all 7 pages then the macro
will work for you. If your data is a mix of blank and non-blank rows in
column A, you would have to look for a pattern that can be coded into the
procedure. If you can send me a sample, I'd be happy to review it.
 
Do you need to insert a row beyween every row?

This would make for difficult sorting, filtering, copying, pasting and other
functions.

Perhaps, if just for appearance, you could double the height of the rows?


Gord Dibben MS Excel MVP
 
Here's a method which is much faster than a macro -- practically
instantaneous:
In an unused column, say G, enter 1 in G1 and 2 in G2. Select G1:G2, use the
fill handle & drag to the bottom of your data (if the column is adjacent to
your data, double-click the fill handle). Say this goes to row 4000. You now
have #s from 1-4000. Copy them, paste them below so you'd have 1->4000 again
in cells G4001:G8000. So now you have 2 sets of #s 1->4000. Select all your
data and SORT by column G. Voila, your data is double-spaced -- Excel sorted
the blank rows into place. Now simply clear column G!!

Bob Umlas
Excel MVP
 
Great solution, Bob. Once again, simplicity and a good practical mind
prevail over macro wizardry!

*************************
 
Back
Top