Inserting a bunch of rows at once.

[Matt]

I have about 1000 rows of data in column A. How do I insert a blank row in between each row, without doing it one at a time? Is there a way to insert alternating blank rows for an entire sheet? Please Help

-Matt

 

[David McRitchie]

Hi Matt,
You won't be able to sort, so you might want to just change the
row height instead. But here is a macro to insert a row
between rows with data. Note in inserting or deleting rows
you start a loop from the bottom. More information in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

and example of what you asked for:

Sub InsertALTrows()
'David McRitchie, misc 2000-01-27
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim i as long
i = ActiveSheet.UsedRange.Rows.Count 'attempt to fix last cell location
For i = Cells.SpecialCells(xlLastCell).Row To 2 Step -1
If Len(Trim(Cells(i, 1))) <> 0 Then Rows(i).Insert ' 1 is Column A
Next i
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

note you can immediately rerun the macro without inserting more rows because it
checks to see if there is a need to insert based on content in Column A cell.

Note the macro is general purpose it does not use your number of about
1000 rows in any manner. It checks for the last row.

To install and use a macro see my Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have about 1000 rows of data in column A. How do I insert a blank row in between each row, without doing it one at a time? Is

there a way to insert alternating blank rows for an entire sheet? Please Help.

 

[otium]

No "normal" way that I can think off.

One "long winded solution" would be to ...
1) Copy all the rows
2) Sort data by column A (you should now have pairs of duplicates wher
the duplicate is where you want the blank row)
3) Add a new column with the formula =MOD(ROW(A1),2)
4) Assuming that your first data cell is A2 this should put a 1 int
the "duplicate" rows.
5) Filter the new column by "1"
6) Highlight all these rows and pres the delete key
7) Remove the filter - you should have desired result

Ala

 

[Ken Wright]

With your data in say A1:J1000

In cell K1 put 1
In cell K2 put 3

Select K1:K2 and drag down to bottom of your data.

In cell K1001 put 2
In cell K1002 put 4

Select K1001:K1002 and drag down to K2000.

Select A1:K2000 and sort in ascending order on Col K, then just delete Col K.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I have about 1000 rows of data in column A. How do I insert a blank row in

between each row, without doing it one at a time? Is there a way to insert
alternating blank rows for an entire sheet? Please Help.

 

[Matt]

That worked great...however, there is a second part to my problem. I have the data spread out in column A, skipping every other line. In column B, I also have data that is skipping every other line. The data is offset by 1 row each. For example A1 is a number like "1005", cell B2 is a dollar value $199.99. I want to merge the two cells together, i.e. move what is in cell b2 to cell a2, without affecting anything in column A. and I want to do it all at once, instead of line by line. Any ideas? Thanks
-Matt from Arizona

 

[David McRitchie]

Hi Alan,

No "normal" way that I can think off.

That was in answer to my posting? Perhaps ExcelTips Forum
references any message in a newsgroup thread rather than the one
you think you are replying to.

Beside taking much longer than a macro, I don't think your
solution would be very usuable. I assumes that the records
can be sorted, and that only one entry per sort key would have been
in the original -- very dangerous assumptions.

But it's up the poster to determine if a solution works for them, and
to test on a copy of file first, rather than risk their data.

Not meant as anything personal it's just part of learning. A lot of
learning is from mistakes.

And Ken posted a similar solution but it would work a lot better as
one would sort on the added column, so the records would not be
put into the wrong order by sorting, but is still a heck of a lot more
work and slower than using a macro. Would still add extra blank
rows if none were needed..

 

[David McRitchie]

Hi Matt,

move what is in cell b2 to cell a2, without affecting anything in column A.

Would you mind clarifying that. Also not clear did you use Alan's
solution or mine (mine was the macro). If you are intending to move
cell B2 into a2.

Did your original data already have alternate between content and
empty cells in column A.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

That worked great...however, there is a second part to my problem. I have the data spread out in column A, skipping every other

line. In column B, I also have data that is skipping every other line. The data is offset by 1 row each. For example A1 is a
number like "1005", cell B2 is a dollar value $199.99. I want to merge the two cells together, i.e. move what is in cell b2 to cell
a2, without affecting anything in column A. and I want to do it all at once, instead of line by line. Any ideas? Thanks.

 

[Matt]

Actually I tried all 3 solutions, but yours was the fastest. Never used the macro thing before, so that's why I tried it last. But it was the easiest, once I figured out how to get the macro set up. As far as my new problem, I have alternating data in both columns, I just want all the data to show up in column A.

It looks like this

A
1 10002
2 $199.0
3 10003
4 $79.9

I want it to be

A
1 10002
2 $199.0
3 10003
4 $79.9

does that clear it up?

 

[Ken Wright]

Select all of Col A, do Edit / Go To / Special / Blanks, then do Edit / Delete /
Shift Cells Left

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Actually I tried all 3 solutions, but yours was the fastest. Never used the

macro thing before, so that's why I tried it last. But it was the easiest, once
I figured out how to get the macro set up. As far as my new problem, I have
alternating data in both columns, I just want all the data to show up in column
A.