Sheets.Cells.Value = Sheets.Cells.Value Should this be very slow?
On 4 Apr, 08:19, "artisdepartis" wrote:
Dear All,
Below is my code for comparing 2 lists.
As i am working in an enviroment with very small dataresources, i am
used to things being slow... However, my code is ultra slow atm. Any
ideas on speeding it up or should i call my techsupport *again* to ask
for more memory?
Dim lastRow As Long, rw As Long
Dim rngA As Range ' rngA = first list of data
Set rngA = Range(Sheets("Deltas").Cells(1, "A"),
Sheets("Deltas").Cells(Rows.Count, "A").End(xlUp))
lastRow = Sheets("ODIS").Cells(Rows.Count, "A").End(xlUp).Row ' = end
of 2nd list of records
MsgBox lastRow
'rw writes the results on Deltas
rw = Sheets("Deltas").Cells(1, "E").End(xlUp).Row
'MsgBox rw
For i = 1 To lastRow Step 1
If Application.CountIf(rngA, Sheets("Deltas").Cells(i, "F").Value)
= 0 Then
Sheets("Deltas").Cells(rw, "G").Value =
Sheets("ODIS").Cells(i, "C").Value
Sheets("Deltas").Cells(rw, "H").Value =
Sheets("ODIS").Cells(i, "E").Value
Sheets("Deltas").Cells(rw, "I").Value =
Sheets("ODIS").Cells(i, "G").Value
Sheets("Deltas").Cells(rw, "J").Value =
Sheets("ODIS").Cells(i, "H").Value
rw = rw + 1
End If
Next
If you find your code to be a bit on the slow side, then you could
always try processing the lists as arrays. Read the lists off the
worksheets, do all the comparisons and copying in an array, then write
the results back to the worksheet. Should speed things up.
|