Copy/paste speed

[Colin Macleod]

I'd appreciate some help in re-writing the following bit of
lookup/copy/paste code so that it runs faster. Typically, StartRow will have
a value of 10, and EndRow could be 4000 or 5000.

For r = StartRow To EndRow
If Application.IsNA(Cells(r, ChanCode_Col)) = False Then
SelChanCode = Cells(r, ChanCode_Col)
RegSht.Select
SelFactorRow = Application.Match(SelChanCode, Range("b:b"), 0)
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste
End If
Next

Thanks very much for any help and suggestions.


Colin

 

[JLGWhiz]

Change this segment:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy
Sheets("Report").Select
Cells(r, 23).Select
ActiveSheet.Paste

To this:

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)

You can put it all on one line by deleting the subscript (underscore) and
just leave a space between Copy and Sheets.

 

[Rick Rothstein]

Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _

Sheets("Report").Cells(r, 23)

This is a little more compact way to write the above line of code...

Cells(SelFactorRow, 6).Resize(SelFactorRow, 13).Copy Sheets("Report").Cells(r, 23)

 

[Colin Macleod]

Thanks for your help on this.