optimalize search with textbox

[danusko]

Hi,
pls. how can i optimalize thic code, that it works faster, better?
and how to change it that it will return only unuique value to column C?

is here possibility how to make faster to search in huge table?
i want to use it for search in big table...i have defined ranges in 2 sheets, but it take a long time ..
what can i do, that will works faster?

e.g. i have this code (but i will define more as one sheet)
and it take a long time to searching only in a few cells..what will do with 7000 row ?

Please Login or Register  to view this content.

thx to all

[royUK]

Not sure why yu don't just use the inbuilt Search - Ctrl->F. If you want code don't use a loop try the Find method

Please Login or Register  to view this content.

[danusko]

i dont care what for method is used..
for me is important only if is it quick and if it works as i want..
i tried method with find (your last code) but i seems, that is not quicker...for you it is?
and second ....can it return only unique values? (now if is in range colun A name repeatedly..it return the same name 4 or 5 times

[danusko]

my fault...
i had more codes in this modul and there was also this command

Please Login or Register  to view this content.

and i thing this make slower searching..
now it search immediately
thx and sorry

[royUK]

my fault...
i had more codes in this modul and there was also this command

Please Login or Register  to view this content.

and i thing this make slower searching..
now it search immediately
thx and sorry

That will make no difference to the speed of the code. You really need to read some basic VBA Tutorials - Option Explicit orces declaration of Variables & the other tells Excel to ignore case differences - you use UCase.

The Find Method is faster than a loop. The code that I posted looks for the string entered in the TextBox & lists all instances in Column C, which is what I though you asked for.

[danusko]

yes, thats truth...
now i figured out, that it is comes very slow, when i open my big data workbook with many sheets, many tables, many function (private sub...) ....
it goes very slow after opening this file...but i don know how to optimize it. If i close this file ...all it works immediately (((