Repeat data in Excel 2

[Andrzejek]

I have some data in Excel that looks like this:

[pre]
A B C D
1 abc xyz 123 gt5
2 cde opr 65 vw
3 njk
4 opi
5 hgf oiu 890 uyt
6 iuy
7 jhg
8 uiy lkj 896 kjl
8 654
[/pre]

I need to fill the data in the missing cells with the data from the row above, i.e. take data in A2:C2 and paste it into rows 3 and 4 (in columns A thru C), and the do the same down the rows. I do know how many rows of data I have to deal with.

I need to end up ti something like this:

[pre]
A B C D
1 abc xyz 123 gt5
2 cde opr 65 vw
3[blue] cde opr 65 [/blue] njk
4[blue] cde opr 65[/blue] opi
5 hgf oiu 890 uyt
6[blue] hgf oiu 890 [/blue] iuy
7[blue] hgf oiu 890[/blue] jhg
8 uiy lkj 896 kjl
8[blue] uiy lkj 896 [/blue] 654
[/pre]

I do it right now with this code, but as you can see there is a lot of Copy/Select/Paste

[green]
'Repeat Main information in empty rows
'intMainRow is the last row of data[/green]
For intTempRow = 2 To intMainRow
    If .Range("A" & intTempRow).Value = "" Then
        .Range("A" & intTempRow - 1 & ":C" & intTempRow - 1).Copy
        .Range("A" & intTempRow).Select
        .ActiveSheet.Paste
    End If
Next intTempRow

But I know there should be a better, faster way to accomplish this.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

Did you try this one line instruction ?
.Range("A" & intTempRow - 1 & ":C" & intTempRow - 1).Copy .Range("A" & intTempRow)

Or perhaps this ?
.Range("A" & intTempRow & ":C" & intTempRow) = .Range("A" & intTempRow - 1 & ":C" & intTempRow - 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

You can:

Dim c As Range
On Error Resume Next
For Each c In ActiveSheet.UsedRange
    If c = "" Then c = c.Offset(-1, 0)
Next c

combo

 

[kjv1611]

Here's another idea. I am not putting the code, b/c I don't remember it offhand, and would have to go find some or rebuild it, but I know I'v done it.

You know how autofill will stop filling when it reaches a populated field? Well, what about following this method:
1. When row 1 of data is selected, check the value for row 2 in the same column for a value.
2. If row 2 is missing a value, autofill down the 2 columns you need to copy down (assuming it's always that 2 columns are missing).
3. Check for the next empty cell in the columns of reference, maybe faster using the Excel Goto setup End(xlUp) or End(xlDown), for instance, and do the same again.

Some further thought would have to be put into it, but that might run pretty quickly.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Better & faster:

Well this might be a copy of a pivot table.

One time deal, I don't sweat the petty things. Just fill out the empty cells on the sheet via formulas/copy/paste.

Recurring stuff, I don't pet the sweaty things! I want to find the source: WHO dunit, and WHAT was his source data. Why mess with an intermediate process???

 

[Andrzejek]

I went with PHV's simple sollution:[tt]
.Range("A" & intTempRow & ":C" & intTempRow).Value =
.Range("A" & intTempRow - 1 & ":C" & intTempRow - 1).Value[/tt]

@ combo, there may be 2, or 3, or 20 empty rows to fill :-(

@ Skip, this is a kind of a report from Oracle DB, and "WHO dunit", well, I dunit. The main problem is: user does not know the final format of this report. Right now it is a 'work in progress, change it as you go, read my mind what I really want' kind of approach.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

If the source is an Oraclr db, the why are you messing around with this particular issue?

 

[combo]

The code from my post will only fail if you need to fill few last empty rows. You can replace UsedRange reference by processed range to avoid this issue.

combo

 

[Welshbird]

You can do this with no code at all you know?

PLace you curser in your data - use [ctrl] & g - the select special then blanks.

In you cell type = then up arrow, then use [ctrl] & enter.

(Just saying!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen