Finding a value and then suppressing ALL rows WITHOUT the found va

Excel 2003 SP3
WinXP Pro SP2

I need to find all entries in a specific column, say U, that have the text
value of '30'. There are about 12K rows and about 150 or less have the text
value of '30' in the particular 'Unnn' cell. Once I've found this text value
of '30' (EditFindFind What=30Search by ColumnsMatch entire cell
contents), I want to suppress all the other rows and print just columns A, B,
C and D as well as U (which contains the text value of '30'). I know how to
Hide rows but how do I hide those rows that do NOT have '30' without manually
doing it?

So:
1) Find all rows with text value '30' in Column U
2) Suppress all rows WITHOUT text value '30' in Column U
3) Print ONLY Columns A, B, C, D and U.
Simple? I have no idea on the hiding aspect except a manual hide which is
too laborious!

TIA!

Here's the manual way:

1. DataFilterAutofilter
2. Click arrow in column U
3. Choose '30' in the dropdown list -- now you are viewing only the
rows that have a '30' in column U
4. Highlight visible rows and copy to new worksheet
5. Delete all columns except A,B,C,D,U
6. Goto FilePrint


HTH,
JP

On Jan 23, 4:09*pm, Tom wrote:
Excel 2003 SP3
WinXP Pro SP2

I need to find all entries in a specific column, say U, that have the text
value of '30'. *There are about 12K rows and about 150 or less have the text
value of '30' in the particular 'Unnn' cell. *Once I've found this text value
of '30' (EditFindFind What=30Search by ColumnsMatch entire cell
contents), I want to suppress all the other rows and print just columns A, B,
C and D as well as U (which contains the text value of '30'). *I know how to
Hide rows but how do I hide those rows that do NOT have '30' without manually
doing it?

So:
1) Find all rows with text value '30' in Column U
2) Suppress all rows WITHOUT text value '30' in Column U
3) Print ONLY Columns A, B, C, D and U.
Simple? *I have no idea on the hiding aspect except a manual hide which is
too laborious!

TIA!

Tom

Select E:T and FormatHide.

DataFilterAutofilter on column U to filter out anything not '30'

FilePrint should get you only A and U with the '30' rows.


Gord Dibben MS Excel MVP

On Wed, 23 Jan 2008 13:09:03 -0800, Tom wrote:

Excel 2003 SP3
WinXP Pro SP2

I need to find all entries in a specific column, say U, that have the text
value of '30'. There are about 12K rows and about 150 or less have the text
value of '30' in the particular 'Unnn' cell. Once I've found this text value
of '30' (EditFindFind What=30Search by ColumnsMatch entire cell
contents), I want to suppress all the other rows and print just columns A, B,
C and D as well as U (which contains the text value of '30'). I know how to
Hide rows but how do I hide those rows that do NOT have '30' without manually
doing it?

So:
1) Find all rows with text value '30' in Column U
2) Suppress all rows WITHOUT text value '30' in Column U
3) Print ONLY Columns A, B, C, D and U.
Simple? I have no idea on the hiding aspect except a manual hide which is
too laborious!

TIA!

Yeah, what he said. :-)


--JP


On Jan 23, 5:40*pm, Gord Dibben gorddibbATshawDOTca wrote:
Tom

Select E:T and FormatHide.

DataFilterAutofilter on column U to filter out anything not '30'

FilePrint should get you only A and U with the '30' rows.

Gord Dibben *MS Excel MVP

Another method of hiding the desired columns without the need to hide them
manually on each occasion would be to manually hide them once, then define
that as a Custom View. I would customize a tool bar or the Menu bar and
place the Custom View icon there. Subsequently, all that would be needed
would be to auto filter the desire rows, then select the Custom View name
from the tool bar icon.