Duplicating Odd Cells EFFICIENTLY

[stevenblanc]

Hey folks,

So I have a raw data dump from my server which unfortunately uses two rows for each record, with the second row containing data in only one cell.

The cell above this one data item has a useless piece of data which i would like to replace with it. Currently I have the functionality I need using copy and paste:

Sub MoveData()
Dim rng1, rng2 As Range
Dim i As Integer

   i = 1

   For i = 1 To 150
      Set rng1 = Range("F" & i)
      Set rng2 = Range("F" & i + 1)
      rng2.Copy
      rng1.PasteSpecial (xlPasteValues)
      i = i + 1
   Next i

End Sub

This systematically duplicates every even row into the odd row preceding it. However, it is awfully time consuming particularly for larger ranges of i.

This sub routine is called during a longer formatting process which handles screenupdating. So screenupdating is off while this running. However it damages the seemlessness of the process.

Any direction as to a more efficient method would be greatly appreciated.

Cheers,


Steven

 

[Brianwarnock]

I don't have an answer but I do have a question .

Don't you need to delete row i+1 after the paste else i=i+2

Brian

 

[stevenblanc]

Brian,

Logic would dictate that yes. And that was my original code (i = i+2). But for some strange reason that does not work. I do delete the rows, but that is handled by a separate sub which does not run until after this one completes.

 

[Brianwarnock]

Of course the For construct has a default of +1 built in you don't need to code it , so your i=i+1 is an additional sum

The full code should have been

For i=1 to 150 step 2

The step can be positive or negative but if omitted defaults to +1

I'm getting old.

Brian

 

[stevenblanc]

Ah, obviously! Too many hours staring at code !

I'll clean that right up.