Insert a blank row between each used row in document

  • Thread starter Thread starter MrsMrfy
  • Start date Start date
M

MrsMrfy

I need a macro to insert a blank row after each row in my document;
that is, every other row will be an inserted row.

For example:

I have:

Row 4 Smith, James P Maintenance $32.75 12
8 4 6 9 ...
Row 5 Mason, Carole Admin $18.56
10 8 10 5 0 ...

and I need

Row 4 Smith, James P Maintenance $32.75 12
8 4 6 9 ...
Row 5 Blank row
Row 6 Mason, Carole Admin $18.75
10 8 10 5 0 ...
Row 7 Blank row

for the entire document (number of entries changes). Thanks.
 
One way is to insert a helper column to the left of your data, then
fill down a number series (1,2,3,etc) next to each row of data. Copy
that number series and paste it immediately below itself. Then sort on
that column. You can then delete the helper column.

Maybe this thread will help...

http://tinyurl.com/2efccx

HTH,
JP
 
One way of doing it without a macro is to fill a sequence 1, 2, 3 etc
down an empty column for as many rows as you currently have (suppose
this is 300). Then copy that sequence down into the next 300 (blank)
cells in the same column. Then you can highlight all 600 rows and sort
them using the sequence number as the key field.

You can then delete the sequence column.

Hope this helps.

Pete
 
It is truly disturbing how similar our replies are.

*slowly backing away from computer*

:-)

--JP
 
Yes, I thought that when I read yours - and only 1 minute difference
between the posts, too <bg>

Pete
 
Yes, I thought that when I read yours - and only 1 minute difference
between the posts, too <bg>

Pete





- Show quoted text -
Click to expand...


Thanks, guys. Your suggestions are very similar and will work very
well for my purpose. I appreciate the help.
 
If you really want a macro solution, then you can turn the macro
recorder on while you do this once (use relative addressing). amd then
in future you can just re-run the macro when required.

Hope this helps.

Pete
 
Here is a macro that inserts a blank row between each row of data - courtesy
of a previous contributor on this group (sorry but I cannot recall their
name at present).

Sub AddRows()

Dim r As Long
Dim LastRow As Long

Application.ScreenUpdating = False
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
'\***You can change LastRow 1 below to stop at any row number ***
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) <> 0 Then Rows(r + 1).Insert
Shift=xlDown
Next r
End Sub
 
Back
Top