Copy/paste speed
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)
--
Rick (MVP - Excel)
"JLGWhiz" wrote in message ...
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.
"Colin Macleod" wrote in message
...
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
|