Is ther a better way?

  • Thread starter Thread starter BN-CD
  • Start date Start date
B

BN-CD

Is there a better way to achieve the same result that doesn't take s
long. I let this run for 10 minutes before stopping it. I changed th
LastRow to = 50 and it ran fine so I know it works. Normally whe
running this there will be between 2000 and 10000 rows to loop through
I know when looping through that many rows it's going to take some tim
but if 2000 rows takes longer than 10 minutes then this won't work wel
for me.

Ok, This is seeing if 2 rows in columns A match then 2 rows in column
C match then 2 rows in columns D match. If all are true then add 1 t
previous rows value. if its not true then put a 1 in the cell.
I'm basically counting the number of times columns A, C, and D matc
with previous row then starting the count over.



Code
-------------------
Dim counter As Integer
LastRow = Range("A65532").End(xlUp).Row
counter = 2
Do Until counter = LastRow + 1
If Range("A" & counter) = Range("A" & counter - 1) And Range("D" & counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" & counter - 1) Then
Range("G" & counter) = Range("G" & counter - 1).Value + 1
Else
Range("G" & counter) = 1
End If
counter = counter + 1
Loo
 
This might help

Dim counter As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LastRow = Range("A65532").End(xlUp).Row
counter = 2
Do Until counter = LastRow + 1
If Range("A" & counter) = Range("A" & counter - 1) And _
Range("D" & counter) = Range("D" & counter - 1) And _
Range("C" & counter) = Range("C" & counter - 1) Then
Range("G" & counter) = Range("G" & counter - 1).Value + 1
Else
Range("G" & counter) = 1
End If
counter = counter + 1
Loop

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

BN-CD said:
Is there a better way to achieve the same result that doesn't take so
long. I let this run for 10 minutes before stopping it. I changed the
LastRow to = 50 and it ran fine so I know it works. Normally when
running this there will be between 2000 and 10000 rows to loop through.
I know when looping through that many rows it's going to take some time
but if 2000 rows takes longer than 10 minutes then this won't work well
for me.

Ok, This is seeing if 2 rows in columns A match then 2 rows in columns
C match then 2 rows in columns D match. If all are true then add 1 to
previous rows value. if its not true then put a 1 in the cell.
I'm basically counting the number of times columns A, C, and D match
with previous row then starting the count over.



Code:
--------------------
Dim counter As Integer
LastRow = Range("A65532").End(xlUp).Row
counter = 2
Do Until counter = LastRow + 1
If Range("A" & counter) = Range("A" & counter - 1) And Range("D" &
Click to expand...
counter) = Range("D" & counter - 1) And Range("C" & counter) = Range("C" &
counter - 1) Then
 
=SUMPRODUCT(--($A$1:$A$2000=$A$2:$A$2001),--($C$1:$C$2000=$C$2:$C$2001),--($D$1:$D$2000=$D$2:$D$2001))

will give you the count if you have values in all the rows. (if you rows
15 to 21 were all blank, for example, then this would show them as matching -
which technically, they are).
 
Back
Top