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' (Edit>Find>Find What=30>Search by Columns>Match 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?

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!





Here's the manual way:

1. Data>Filter>Autofilter
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 File>Print


Gord Dibben


Select E:T and Format>Hide.

Data>Filter>Autofilter on column U to filter out anything not '30'

File>Print should get you only A:D 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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question