Copy and Paste Problem

Posted by Rick Langston on May 20, 2001 9:26 AM

Greetings All

This is my problem:

I have a worksheet that contains about 6,000 lines.
I found a code snippet to automatically insert a blank line between each one. But here is what I would want to do next. Copy data from the following cell, paste into the cell above and move on to the next e.g.

A1-A5

test 1

test 2

test 3

test 4

test 5
( This is how it looks after the line insertion. )

test 1
test 2
test 2
test 3
test 3
test 4
test 4
test 5
test 5

This is how it should after the copy paste procedure.

Any ideas would be appreciated.

Rick

Posted by Dave Hawley on May 20, 2001 9:52 AM

Hi Rick

This code will do it.

Sub FillIn()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim rFill As Range
Dim rCell As Range

Set rFill = Range("A1", Range("A65536").End(xlUp)) _
.SpecialCells(xlCellTypeBlanks)

rFill = "=R[-1]C"

Range("A1", Range("A65536").End(xlUp)) = _
Range("A1", Range("A65536").End(xlUp)).Value

Set rFill = Nothing
End Sub

As you can see it's written for Column A

Dave
OzGrid Business Applications

Posted by Rick Langston on May 20, 2001 10:56 AM

Thanks for the speedy reply Dave.

Would it be possible to fill these cells from data
collected from the cell just below the added line?

Posted by Dave Hawley on May 20, 2001 11:26 AM

Would it be possible to fill these cells from data

Hi Rick

It sure is possible! Change this line:
rFill = "=R[-1]C"
To
rFill = "=R[1]C"

Dave

OzGrid Business Applications

Posted by Rick Langston on May 20, 2001 11:38 AM

Again I thank you

this will surely help.

Posted by Annie on May 20, 2001 3:14 PM

Rick
What code are you using to insert the blank rows? Can you post, please?
Annie

Posted by Rick Langston on May 21, 2001 4:38 AM

Hi Annie

The code for inserting a new line was found here and here it is.

*************************
Sub Insert_row()
Dim Number_of_rows

Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("A2").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value &LT;&GT; Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 1
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

End Sub

Posted by Eric on May 21, 2001 10:58 AM

I know you have plenty of help with this already, but...

There are a lot of ways to skin the cat in excel, and here's the one that came to mind when I saw your problem:
test 1

test 2

test 3

test 4
copy your column over in the adjacent column, but one line offset, so you have
test 1
test 1
test 2
test 2
test 3
test 3
test 4
test 4
Then in the 3rd column (assume these are columns A, B, and C and that they start in row 1) write the formula =if(a1>b1,a1,b1) and the third column should give you the merge of the other two. Just remember to "paste as values" before you start moving column c around.

Posted by Eric on May 21, 2001 11:03 AM

should read

There are a lot of ways to skin the cat in excel, and here's the one that came to mind when I saw your problem:
This should read
:. test 1 : This is how it should after the copy paste procedure. : Any ideas would be appreciated. :