how do i delete all non filtered rows in Excel

Question

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

I think you'll probably have to do that in VBA. – Lance Roberts Jun 01 '10 at 19:22 — Lance Roberts, Jun 01 '10 at 19:22

score 17 · Accepted Answer · answered Jun 01 '10 at 19:39

17

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.

answered Jun 01 '10 at 19:39

Sux2Lose

3,327

3

Ctrl+G is the hotkey for "Go to..." in excel 2003 – Jarvin Jun 01 '10 at 20:26
I couldn't find this in the newest version of Excel, but the answer here helped me: https://superuser.com/questions/387484/how-to-delete-rows-not-in-filter – MakotoE Feb 24 '23 at 00:10

score 2 · Answer 2 · answered Jun 19 '12 at 14:50

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

answered Jun 19 '12 at 14:50

Danielle

21

score 1 · Answer 3 · answered Sep 17 '14 at 12:28

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.

answered Sep 17 '14 at 12:28

Guest

11

how do i delete all non filtered rows in Excel

3 Answers3

Linked