Maybe you could use pivottables.

On 08/19/2010 10:59, JT wrote:
Thanks Don

I would usually use an array or sumproduct formula approach to
generate my filtered list but the sheer volume of data in this case
makes it too slow.

I'd be interested to hear of any macro based approaches other than the
2 I highlighted in my original e-mail.

Thanks for the offer of looking at the file but unfortuately I can't
send it due to confidentiality issues.

Thanks

John


On 19 Aug, 15:39, Don Guillett Excel
wrote:
On Aug 19, 7:52 am, wrote:





Thanks

Good ideas but I think I'm going to go with my first approach as I
need to drive calculations off the results.

On 19 Aug, 12:26, Dave wrote:

I like your first choice.

But there are alternatives.

You could copy the entire sheet, then delete the rows you don't want.

You could copy the entire sheet, sort the data by that column (or add an column
with a formula that indicates whether to keep the row or not). Then delete the
rows you don't want (and delete the helper column if you used it).

On 08/19/2010 04:22, JT wrote:

One of the sheets in my excel model contains a long list of data. I
need some code to copy the data into another sheet, but exclude the
rows where one of the columns contains the words “Don’t Work”. I can
think of 2 ways of doing this:

a) Writing a macro that applies a filter to the source worksheet then
copies and pastes the data into the destination sheet (a bit messy);
or

b) Writing a macro that cycles through the source rows one by one,
adding the row to a union range if it doesn’t contain “Don’t work”,
then dropping the data from in this range into the destination sheet
(I have used a similar approach to this before and found it very slow
with 20k+ rows).

The query is whether there is a smarter and quicker way of doing this
available?

I know this would be straightforward in Access, but my brief is to
prepare this model in excel.

Thanks

John

--
Dave Peterson- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Depending on what you want you may be able to withOUT copying to
another sheet, use sumproduct or array formulas or a macro to get the
info desired.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text

--
Dave Peterson