Is ther a better way?

[BN-CD]

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.

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
Loop

Any help would be greatly appriciated.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" &
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
> --------------------
>
>
> --
> BN-CD
> ------------------------------------------------------------------------
> BN-CD's Profile:
http://www.excelforum.com/member.php...o&userid=35374
> View this thread: http://www.excelforum.com/showthread...hreadid=570815
>

[Tom Ogilvy]

=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).

--
Regards,
Tom Ogilvy



"BN-CD" wrote:

>
> 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" & 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
> --------------------
>
>
> --
> BN-CD
> ------------------------------------------------------------------------
> BN-CD's Profile: http://www.excelforum.com/member.php...o&userid=35374
> View this thread: http://www.excelforum.com/showthread...hreadid=570815
>
>

[BN-CD]

Thanks for the help guys. It works great!