Copy/paste speed

  • Thread starter Thread starter Colin Macleod
  • Start date Start date
C

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
 
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.
 
Range(Cells(SelFactorRow, 6), Cells(SelFactorRow, 18)).Copy _
Sheets("Report").Cells(r, 23)
Click to expand...

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)
 
Back
Top