up vote 1 down vote favorite
share [g+] share [fb]

i have a very large table in excel (1000's of rows) and i filter it to only show 10 rows. I wonder if there is a way to delete the rows not shown [ ie dont meet filter conditions]? this would enable me to reduce the file size before i send it.

update

ok to explain why i cant just copy:

many thousands of rows down under the table the user has created complex formulas and graphs which wont carry if i copy across to another worksheet if i just copy the rows :*(

david

link|improve this question
100% accept rate
If the data is required for the "complex formulas and graphs", you can not delete the rows. These constraints seem to eliminate most ways to answer your question. You need to ask yourself what you are willing to give up in order to reduce the file size? – CharlieRB Feb 8 at 16:04
feedback

3 Answers

up vote 3 down vote accepted

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 enter image description here

  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

enter image description here

link|improve this answer
see update as to why this solution wont work for me – GreyCloud Feb 8 at 14:26
@GreyCloud: As per your update, i have updated my answer. check it out – Siva Charan Feb 8 at 15:09
Impressive stuff, i would never have found this. however running this it says "could not complete" and then gives warnings about messed up references and the graph data disappears :-/ – GreyCloud Feb 8 at 15:34
It seems that some of the visible cells use data stored in hidden cells, therefore you can't just copy the visible cells and paste them into a new workbook, and you can't just delete the hidden cells. Copy the visible cells as explained above, then in the new workbook "paste special" and then "paste values". This inserts values rather than formulas, this should work for you – ssollinger Feb 8 at 15:49
Very nicely done! – Jesse Feb 8 at 17:23
feedback
up vote 0 down vote

This might be overly simplistic but why not just copy/paste the 10 rows you've filtered down to into a new spreadsheet?

link|improve this answer
because many thousands of rows down the user has created complex formulas and graphs which wont carry if i copy across :*( – GreyCloud Feb 8 at 14:25
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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