Find all cells with given text and how to do the opposite of Intersect

[Leshak]

Hi,

Could you help me with these 3 issues I'm having pls?

1) How do you find ALL cells with given text in one column?
(result as Range, call it "types")

2) How do you create a second range (call it "features"), which is based to the "types" range and which would contain the following cells: features(B1, C1, D1, B3, C3, D3, B4, C4, D4, B7, C7, D7) if the first range "types" would consist of all cells in A column with text "type1": types(A1, A3, A4, A7)

r/c A B C D
1 type1 1 a r
2 type2 2 b s
3 type1 3 c t
4 type1 4 d u
5 type2 5 e v
6 type3 6 f w
7 type1 7 g x
8 type2 8 h y

I would like to avoid looping all cells in a for loop because my data sets are thousands of rows long, tens of columns wide, and speed is a critical issue.

3) I have also tried to find an opposite function to Intersect like "Exclude", which would take two ranges, and return a range, which consists of those cells which are only in either of the given ranges and not in both. Do you know if there's one?

[StephenR]

1) Find method.
2) No idea what that means.
3) AFAIK there is no single method, but it could be worked around.

[DonkeyOte]

Could you not apply an Auto Filter?

First range being column 1 visible cells
Second range being columns 2:4 visible cells

Given volume of data and fact that altering Row Visibility is Volatile action you should definitely toggle events etc....

If you can you should consider sorting the data by Type first such that you have a contiguous range.

[Leshak]

Great thanks to both! Autofilters are superb! It took me a while to learn using autofilters and test with large data sets. Using them cut down processing time about 80-90% compared to For loops. Thanks guys for helping me out!

[Leshak]

Here's what I did in case someone is interested of using autofilters in similar conditions. The attachment shows the test setup:

Please Login or Register  to view this content.