Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

Dear Gord,

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion". I did
DataFilterAutofilter. A check appeared beside the Autofilter indicator,
but nothing else happened. Is there any more you can add to your reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Dear Gord,

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Thanks Roger.

That's about it, although OP does mention multiple columns which might give a
wrinkle.

Wait and see.


Gord

On Fri, 1 Jun 2007 00:37:09 +0100, "Roger Govier"
wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

Dear Roger,

Thanks! I think I've got it now; and also by reading about "filter" on
Excel's built-in help.

I just clicked a cell in one of the relevant columns, did
DataFilterAutofilter, then clicked on the arrow that appears in each column
when you do that, clicked "custom", saw that pop-up menu, did "greater than"
"0", and that seems to have done the work for me.

Only two remaining questions:

1. I'm not sure what you mean by "mark the block of data". Rather than
checking for values greater than zero in one column, it would be more ideal
for my purposes if I could filter for values greater than zero in more than
one column simultaneously. If I understand you correctly, how could I "mark
the block of data" I wish to perform the analysis on so as to include more
than one column? In other words, how do I filter for data greater than zero
in column A, B, and C, and filter out any row that has a value greater than
zero in at least one of those columns?

2. Once I have done this, how do I get the worksheet back to normal--i.e.,
the way it was before I did the filtering? I've been closing the document
without saving, and then re-opening it every time I want to play around with
the filtering device so as not to destroy the previous results. What a
nuisance!

Mick

"Roger Govier" wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Dear Gord,

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Hi Mick

What I meant was select the complete range of your data before applying
Autofilter, then you will get the dropdowns on each column within the
range.
You can apply filters to as many columns as you wish, each time it will
reduce the selection to those rows that match each of the criteria you
have applied.

Clicking the dropdown on the column again, and selecting All will remove
the filter from that particular column.
DataFilterAutofilter again, will remove all filters - it is a toggle,
either to switch it on or off but this removes the dropdowns altogether.

I have dragged a Show All command to my toolbar, which I can click to
remove all filters at once, whilst leaving the dropdowns in place.

ViewToolbarsCustomiseCommandsData drag the Show All to your
toolbar. It appears as Text only, but whilst you are still in Customise
mode, you can right click on the Text Show All and choose an icon if you
wish.

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Dear Roger,

Thanks! I think I've got it now; and also by reading about "filter"
on
Excel's built-in help.

I just clicked a cell in one of the relevant columns, did
DataFilterAutofilter, then clicked on the arrow that appears in each
column
when you do that, clicked "custom", saw that pop-up menu, did
"greater than"
"0", and that seems to have done the work for me.

Only two remaining questions:

1. I'm not sure what you mean by "mark the block of data". Rather
than
checking for values greater than zero in one column, it would be more
ideal
for my purposes if I could filter for values greater than zero in more
than
one column simultaneously. If I understand you correctly, how could I
"mark
the block of data" I wish to perform the analysis on so as to include
more
than one column? In other words, how do I filter for data greater
than zero
in column A, B, and C, and filter out any row that has a value greater
than
zero in at least one of those columns?

2. Once I have done this, how do I get the worksheet back to
normal--i.e.,
the way it was before I did the filtering? I've been closing the
document
without saving, and then re-opening it every time I want to play
around with
the filtering device so as not to destroy the previous results. What
a
nuisance!

Mick

"Roger Govier" wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Dear Gord,

Thank you for your prompt reply. Unfortunately, I think I need a
more
detailed response. I am not sure how to "filter on [my]
criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my
task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?