inserting multiple blank rows on alternate rows & multiple formulas

  • Thread starter Thread starter bob green
  • Start date Start date
B

bob green

I am hoping for some advice regarding the most efficent way to perform
the following task.

I have data that looks like this:

a
b
c
d
e


I want it to look like this (every second row is blank)

a

b

c

d

e

How can I do this in EXCEL? This relates to a second question I have,
I want to run the following command =CONCATENATE (a1,b1,c1,d1,e1,f1)
for 1270 people. Other than performing 1270 search & replaces for the
original formula how can I more efficently do this?

Any assistance is appreciated,

Bob
 
Bob, here is a macro that will insert a row between data in column A, is
this what you need?
Is the next formula you want =CONCATENATE(A2,B2,C2,D2,E2,F2)? if so just
drag the first formula down the rows

Sub Insert_row()
'****will insert 1 row between rows with data in column A,
On Error Resume Next
Application.ScreenUpdating = False
Range("A1").Select
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Range("A1:IV" & 1).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
You may want to reconsider. If you're only adding the lines to make the output
look double spaced, you may want to just double the rowheight.

It'll make some things easier later (autofilters/sorts/subtotals/pivottables).
 
Assume you data in in a sheet named Sheet1

go to a second sheet and enter in cell A1

=IF(MOD(ROW(),2)=1,OFFSET(Sheet1!$A$1,(ROW()+1)/2-1,COLUMN()-1),"")

Then drag fill down and across until you run out of data.

you can then select the data and do Edit=>Copy, then Edit=>Pastespecial and
select values to replace the formulas with the values.

for the second question, I assume this is in row1. Just select the cell
with the formula and drag fill it down to row 1270 and the formula should
adjust automatically.
 
Back
Top