inserting lines throughout a spreadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kerry - 18 Oct 2006 16:21 GMT

If I have a spreadsheet and I make it where there are blank cells between by
data, I then sort and the blank lines are then removed. How do I easily
insert blank cells throught by spreadsheet without physically inserting the
row on every line.

Reply to this Message

Otto Moehrbach - 18 Oct 2006 19:37 GMT

Kerry
This macro will do that for you. I assumed you have data in Column A
starting with A2 (A1 is a header). Change the column and row as needed.
HTH Otto
Sub InsertBlank()
Dim RngColA As Range
Dim c As Long
Application.ScreenUpdating = False
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
RngColA(c).Offset(1).EntireRow.Insert
Next c
Application.ScreenUpdating = True
End Sub

> If I have a spreadsheet and I make it where there are blank cells between
> by
> data, I then sort and the blank lines are then removed. How do I easily
> insert blank cells throught by spreadsheet without physically inserting
> the
> row on every line.

Reply to this Message

kerry - 18 Oct 2006 20:37 GMT

The macro worked greated. However, I do my sort, run the macro, then it does
not keep the sort. it puts it back in the same order it was in. Can you
sort, run macro, and have it keep the sort you ran?

> Kerry
> This macro will do that for you. I assumed you have data in Column A
[quoted text clipped - 16 lines]
> > the
> > row on every line.

Reply to this Message

Otto Moehrbach - 18 Oct 2006 23:07 GMT

Kerry
The macro I gave you does nothing with the sort. It simply takes what's
there and inserts a blank row after every row of data you have. If your
data is being sorted after my macro runs, then you must have other macros
running after mine. I don't know what your file has or how it operates so I
can't advise you there, but the macro I sent you does not sort anything.
HTH Otto

> The macro worked greated. However, I do my sort, run the macro, then it
> does
[quoted text clipped - 23 lines]
>> > the
>> > row on every line.

Reply to this Message