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

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

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 Peterson 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 -

On Aug 19, 7:52*am, JT 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 Peterson 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."

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 MVP
wrote:
On Aug 19, 7:52*am, JT 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 Peterson 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 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

This post might be of interest...
http://groups.google.com/group/micro...026dd7ef9749b#
August 14, 2010 by RB Smissaert - "Get filtered range into array"
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion add-in

..
..
..

"JT"
wrote in message
...
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

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

I'd be interested to hear of any macro based approaches other than the

Google is always at your disposal, please check:

http://groups.google.com/group/micro...rch+this+group

Rgds

On 8/19/2010 8:59 AM, 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.
<snip

How many rows of data do you have? I was going to suggest you copy the
Worksheets(N).UsedRange into a string, split the string into an array
using the vbCrLf character (so each row is in its own array element),
then loop through the array writing any lines that don't contain the
string you mentioned to the filtered data sheet.

alldata() = Split(usedrangedata, vbCrLf)

Will that approach work for you?

Mike

Dear all

Great responses, thanks.

Dave - A pivot table was something I considered but as I'm driving
calculations off the results I'm concerned that it would destabilise
the model if a future user changed the inputs or the pivot table
settings.

Mike - your string suggestion is a fantastic idea. Simple but
genius. I'm working with c. 25k lines with flexibility for the user
to expand up to 90k so I'll give it a go and see how fast it is.

There are also some good suggestions in the previous posts that some
of you posted links to. I'll have a thorough look at them when I get
some time. I generally try to avoid posting here unless I can't find
a solution somewhere else, but it isn't always easy to find what I'm
looking for so thanks for pointing me in the right direction.

Hopefully anyone with a similar problem in the future will find a
wealth of good suggestions here.

Thanks again

John

On 20 Aug, 10:28, Mike S wrote:
On 8/19/2010 8:59 AM, 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.

<snip

How many rows of data do you have? I was going to suggest you copy the
Worksheets(N).UsedRange into a string, split the string into an array
using the vbCrLf character (so each row is in its own array element),
then loop through the array writing any lines that don't contain the
string you mentioned to the filtered data sheet.

alldata() = Split(usedrangedata, vbCrLf)

Will that approach work for you?

Mike

On 8/20/2010 11:08 AM, JT wrote:
<snip
Mike - your string suggestion is a fantastic idea. Simple but
genius. I'm working with c. 25k lines with flexibility for the user
to expand up to 90k so I'll give it a go and see how fast it is.
<snip

If you send me a sheet with just one row of data - obfuscating anything
proprietary - I'll get the macro working then send it to you so you can
try it on your real data.