Copying Data from a row above...

[Guest]

I have a very complex and large spreadsheet that I am trying to edit. Through
several different steps and macros we have things pretty close to where we
need them with one exception.

We need to do a search (in this example - on column 3) and if the results of
the search are a blank space, we want to copy the data from column 4 in the
ROW ABOVE and paste it into the blank cell in column 3.

Ultimately we would love to copy all of the rest of the data and put it in
the same row. The only difference from the two rows is that we would have
copied the contents of one cell twice - to the 3th column and the 4th column.
Everything else would just drop down to the next row...

It is hard to describe, so I have created a "before and after" view of what
we are trying to acomplish...

BEFORE:

Row 1 IBM Ipaq Family Model 11 Model AA Product XL
K9a478
Row 2 IBM Ipaq Family Model 55 Model GG Product SD
UUa771
Row 3
Row 4 HPC Ipaq Family Model 49 Model FF Product TR
JHa888
Row 5
Row 6 KFC Ipaq Family Model 61 Model TT Product VZ
JHa546

AFTER:

Row 1 IBM Ipaq Family Model 11 Model AA Product XL
K9a478
Row 2 IBM Ipaq Family Model 55 Model GG Product SD
UUa771
Row 3 IBM Ipaq Family Model GG Model GG Product SD
UUa771
Row 4 HPC Ipaq Family Model 49 Model FF Product TR
JHa888
Row 5 HPC Ipaq Family Model FF Model FF Product TR
JHa888
Row 6 KFC Ipaq Family Model 61 Model TT Product VZ
JHa546

THANKS FOR YOUR HELP!!!!

 

[Trevor Shuttleworth]

Try:

Sub CopyRow()
Dim i As Long
For i = 2 To Range("A65536").End(xlUp).Row
If Range("C" & i).Value = "" Then
Range(Cells(i - 1, 1), Cells(i - 1, 6)).Copy _
Range("A" & i)
Range("C" & i) = Range("D" & i)
End If
Next 'i
End Sub

Tests between row 2 and the last row, assuming that there is a header in row
1

Regards

Trevor

 

[Guest]

WOW!!!!!!!!!

THAT WAS ABSOLUTELY BEAUTIFUL TREVOR!!

A very elegant solution. It did exactly what I needed (with one tiny, tiny
exception)

Actually it worked exactly the way I described it, but the column actually
go out to column "R"...

Would I just change the formula

Range(Cells(i - 1, 1), Cells(i - 1, 6)).Copy _

to:

Range(Cells(i - 1, 1), Cells(i - 1, 16)).Copy _

I changed the 6 to a 16...


Thanks so much for all of your help. You save me days of boredom, my eyes
from exploding and my hands from falling off...