How to delete rows not in filter

Solution 1:

Try this way for a quick solution:-

1. Copy the filtered 10 results into another sheet
2. Delete the actual sheet

EDIT:

As per the update, below are the steps:-

1. Before starting, take a backup copy of excel sheet
2. Assuming you are filtered all the records and showing only 10 Rows
3. Remaining 1000's are hidden
4. Click on Office Button
5. Click on Prepare option
6. Click on Inspect Document
7. Refer this screenshot, how it looks
8. Click on Inspect button
9. You will see a option "Hidden Rows and Columns" with "Remove All" button
10. Click on Remove All button
11. Click on close button
12. Finally if you see, it has removed all "Hidden Rows and Columns"

Refer this screenshot

Note:

In Office 2010, Inspect Document can be found here:

Solution 2:

The way that worked for me was, assuming the filter is easy to reverse:

1. Clear your filter.
2. Create a temporary column, say called 'TEMP ORDER'.
3. Set every value in that column to 0
4. Reverse your filter (filter for everything you want to delete)
5. Set every value in the 'TEMP ORDER' column to 1 on the filtered results
6. Clear your filter.
7. Sort your data by the 'TEMP ORDER' column, smallest to largest.
8. Find on which row the first '1' occurs
9. Resize your table (Design tab), having the last row be the row before the first '1'
10. Delete the rows that are no longer in your table.

This may be a preferable solution if you don't want to mess up any other sheets in your workbook and are concerned about what might happen if you copy and paste your data around.

Solution 3:

Why not just copy visible cells to a new sheet? Go to:

quick access tool bar drop down → more commands → commands not in the ribbon → select visible cells → add

When you click this it will select everything that is visible and you can copy and paste everything that's visible.