9

if i have an excel sheet and i filter by a certain column, how do i then delete all non visible rows?

1
  • 1
    I think you'll probably have to do that in VBA. – Lance Roberts Jun 1 '10 at 19:22
13

When you have all your filters selected you can highlight what remains and then click:

Excel 2007:

Home>Editing>Find & Select>Go to special...>Visible Cells Only

Excel 2003:

Edit>Go To...>Special...>Visible Cells Only

Copy and paste into a new sheet.

1
  • 2
    Ctrl+G is the hotkey for "Go to..." in excel 2003 – Jarvin Jun 1 '10 at 20:26
2

Reverse filter on what you don't need and delete those rows. All that will be left are the rows you need - Excel 2007

1

A nice, quick technique: use the Fill Color icon to fill all the cells in one of the columns you see. Then do a color filter on that column, and delete all the rows. You will be left only with the filtered rows.

Not the answer you're looking for? Browse other questions tagged or ask your own question.