Is this slow code?
 

Hi all,

Is my implementation the best way to do this?? My code works but it is very
very
slow - but my user base which will use the code are al on P2's as well!

I am trying to achieve the following:

I have a list of 30 items in a col in a spread sheet.
I have 1 other cell which are constants
I have a [5][1000] table on another sheet in the spready, of which I am
retreiving only values from 2 columns.

For each item in the 30 column list, I want to check all 1000 rows, to
determine if any of the rows match the item, and the 1 constant. If a match
is found then I'm done and I break out.


Am I doing something really ineffcicent?

any help

Thanks

Tom



'Set up the loop. This is a decrementing loop becuase we want to delete
items out of it _
with out effecting the index
'Note the list stops at 2 to ensure the Heading is not removed.
For listCount = listLoop To 2 Step -1
' set the delete condtion to true
DeleteLogic = True
' get the item at the bottom of the list
listValue = Sheet13.Range(listRange & listCount)

' Set up the second loop which will loop through the entire config
table this loop _
increments
For rConfigCount = 2 To rConfigLoop
' Perform the test: If the workstream and list values exsist in
a config row _
then do not delete
If Trim(listValue) < "" _
And _
rConfig.Cells(rConfigCount, configCheck1).Value = listValue _
And _
rConfig.Cells(rConfigCount, configCheck2).Value = userSelection
_
Then
' if a match is found then I don't want to delete
DeleteLogic = False
' if a match is found there is not point searching the rest
of the table.
Exit For
End If

Next rConfigCount
'Perform delete if necessary
If DeleteLogic = True Then _
Sheet13.Range(listRange & listCount).Delete xlShiftUp

Next listCount

Is this slow code?
 

Am I doing something really ineffcicent?

Yes, you are using VBA :-)

From your description I would say that this can probably be done instantly using
existing functions such as COUNTIF or MATCH or SUMPRODUCT / VLOOKUP etc. If you
can give us a bit more info regarding your data with maybe some examples and
ranges, then we can probably help you do this quite easily. If you are hard
over that you need to use VBA then post the code you are using. If you are
looping through all the cells then you may want to use the Find method. If you
are selecting cells then don't as this will slow things down enormously. Give
us a bit more to play with here and we'll get you sorted ;-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Tom" wrote in message
...
Hi all,

Is my implementation the best way to do this?? My code works but it is very
very
slow - but my user base which will use the code are al on P2's as well!

I am trying to achieve the following:

I have a list of 30 items in a col in a spread sheet.
I have 1 other cell which are constants
I have a [5][1000] table on another sheet in the spready, of which I am
retreiving only values from 2 columns.

For each item in the 30 column list, I want to check all 1000 rows, to
determine if any of the rows match the item, and the 1 constant. If a match
is found then I'm done and I break out.


Am I doing something really ineffcicent?

any help

Thanks

Tom



'Set up the loop. This is a decrementing loop becuase we want to delete
items out of it _
with out effecting the index
'Note the list stops at 2 to ensure the Heading is not removed.
For listCount = listLoop To 2 Step -1
' set the delete condtion to true
DeleteLogic = True
' get the item at the bottom of the list
listValue = Sheet13.Range(listRange & listCount)

' Set up the second loop which will loop through the entire config
table this loop _
increments
For rConfigCount = 2 To rConfigLoop
' Perform the test: If the workstream and list values exsist in
a config row _
then do not delete
If Trim(listValue) < "" _
And _
rConfig.Cells(rConfigCount, configCheck1).Value = listValue _
And _
rConfig.Cells(rConfigCount, configCheck2).Value = userSelection
_
Then
' if a match is found then I don't want to delete
DeleteLogic = False
' if a match is found there is not point searching the rest
of the table.
Exit For
End If

Next rConfigCount
'Perform delete if necessary
If DeleteLogic = True Then _
Sheet13.Range(listRange & listCount).Delete xlShiftUp

Next listCount





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004

Is this slow code?
 

Sob Sob - I'm getting too tired for this - Just scrolled down and saw your code
:-( LOL

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004

Is this slow code?
 

Assuming it doesn't necessarily have to be code:-

With your 30 values in say A1:A30, and your constant in any cell that you have
named MyConst, and assuming your two ranges of data you are matching against are
named Col1 and Col2, then in cell B1 put the following and copy down to cell B30

=SUMPRODUCT(($F$2:$F$28=A2)*($H$2:$H$28=MyConst))

In any other cell you can either sum the data in B1:B30 and anything over 0
means a match, or you can do a COUNTIF and pick up on any value greater than 0.

I wasn't clear on whether you needed to actually delete any rows, or were just
looking for a match.

As for code, I would possibly just loop through the 30 values, using the Find
method with each of those values to determine if that value existed in the
relevant column of the 5*1000 range, and then for each record found, simply use
the offset value to check to see if the other column matched the constant,
breaking out when a match is found.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
Sob Sob - I'm getting too tired for this - Just scrolled down and saw your
code
:-( LOL

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004

Is this slow code?
 

=SUMPRODUCT(($F$2:$F$28=A2)*($H$2:$H$28=MyConst))

OK I give up - I'm going to bed.

Make that:-

=SUMPRODUCT((Col1=A2)*(Col2=MyConst))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" wrote in message
...
Assuming it doesn't necessarily have to be code:-

With your 30 values in say A1:A30, and your constant in any cell that you have
named MyConst, and assuming your two ranges of data you are matching against
are
named Col1 and Col2, then in cell B1 put the following and copy down to cell
B30

=SUMPRODUCT(($F$2:$F$28=A2)*($H$2:$H$28=MyConst))

In any other cell you can either sum the data in B1:B30 and anything over 0
means a match, or you can do a COUNTIF and pick up on any value greater than
0.