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 <> 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
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. :