Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
After you filter the records (using either AutoFilter or Advanced Filter)
Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Hi Allen
Work on a Copy of your data - just in case!! DataFilterAutofilter use the dropdowns to select the rows you want to delete, rather than hide those rows. Then, with only those rows visible, select the complete range of visible rows by using the rows numbers at the left of the screen,Right ClickDelete DataFilterShow All and you will be left with just the rows you wanted to retain. -- Regards Roger Govier "Allen" wrote in message ... I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Important Reiteration:
The method I described deletes the VISIBLE filtered cells. To delete the hidden rows, you'd need to alter the filter so only the rows to be deleted display. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Thanks, but I want to delete the HIDDEN records and keep the visible ones;
sorry for not being clearer. Is there a way to do that? Allen "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Change your filter to show the ones you want to remove.
Instead of equals, use does not equal. Instead of contains, use does not contain. Allen wrote: Thanks, but I want to delete the HIDDEN records and keep the visible ones; sorry for not being clearer. Is there a way to do that? Allen "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Hi Allen
What I described, and Ron in his revision, was that you basically reverse your filtering. Make the filters the opposite of what you want, so it is the rows that you don't want that are visible. Then delete those visible rows. -- Regards Roger Govier "Allen" wrote in message ... Thanks, but I want to delete the HIDDEN records and keep the visible ones; sorry for not being clearer. Is there a way to do that? Allen "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Thanks for clarifying that, Dave and Roger.
My second post only made sense if you already knew what I was trying to say..(obviating my need to say it!) *********** Regards, Ron XL2002, WinXP "Roger Govier" wrote: Hi Allen What I described, and Ron in his revision, was that you basically reverse your filtering. Make the filters the opposite of what you want, so it is the rows that you don't want that are visible. Then delete those visible rows. -- Regards Roger Govier "Allen" wrote in message ... Thanks, but I want to delete the HIDDEN records and keep the visible ones; sorry for not being clearer. Is there a way to do that? Allen "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
I recently switched from Excel 2003 to Excel 2007. The process described
below worked in Excel 2003, but has not worked for me in Excel 2007. After filtering records, I highlight the rows I want to delete, then "right-click" and "delete rows." In addition to the highlighted rows being deleted, the non-filtered (hidden) rows which are found after my first highlighted row, and before the last hightlighted row, are also deleted. For example, assume there are 50 populated rows. Using autofilter, rows 5, 17 and 20 are visible. I highlight rows 5, 17 and 20, then delete these rows. The result is that rows 5 through 20 are all deleted, not just rows 5, 17 and 20. Is there a default setting in Excel 2007 that controls for this change? I am using Micorsoft Office Excel 20070 (12.0.6300.5000) SP1 MSO (12.0.6213.1000) Thanks! Jeff "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
After highlighting the rows hit F5...special...visible cells only...Ok. then
rightclick with the selection on and chose delete. "JDOlson" wrote: I recently switched from Excel 2003 to Excel 2007. The process described below worked in Excel 2003, but has not worked for me in Excel 2007. After filtering records, I highlight the rows I want to delete, then "right-click" and "delete rows." In addition to the highlighted rows being deleted, the non-filtered (hidden) rows which are found after my first highlighted row, and before the last hightlighted row, are also deleted. For example, assume there are 50 populated rows. Using autofilter, rows 5, 17 and 20 are visible. I highlight rows 5, 17 and 20, then delete these rows. The result is that rows 5 through 20 are all deleted, not just rows 5, 17 and 20. Is there a default setting in Excel 2007 that controls for this change? I am using Micorsoft Office Excel 20070 (12.0.6300.5000) SP1 MSO (12.0.6213.1000) Thanks! Jeff "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Thanks for the input, Ron. My version of Excel 2007 does not have an "Edit"
dropdown menu. I've tried "right-clicking" the rows as you describe, but the hidden rows are also deleted. Do you have any other suggestions? Jeff "JDOlson" wrote: I recently switched from Excel 2003 to Excel 2007. The process described below worked in Excel 2003, but has not worked for me in Excel 2007. After filtering records, I highlight the rows I want to delete, then "right-click" and "delete rows." In addition to the highlighted rows being deleted, the non-filtered (hidden) rows which are found after my first highlighted row, and before the last hightlighted row, are also deleted. For example, assume there are 50 populated rows. Using autofilter, rows 5, 17 and 20 are visible. I highlight rows 5, 17 and 20, then delete these rows. The result is that rows 5 through 20 are all deleted, not just rows 5, 17 and 20. Is there a default setting in Excel 2007 that controls for this change? I am using Micorsoft Office Excel 20070 (12.0.6300.5000) SP1 MSO (12.0.6213.1000) Thanks! Jeff "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
GG
Thanks for the input. I tried your suggestion with mixed results. Sometimes it deletes only the visible rows, but not always. Do you have to click "visible cells only" every time you want to delete the visible cells? I'm hoping there is a fix that does not require the extra steps to "F5..special...visible cells only." It seems strange that an upgrade from 2003 to 2007 would require so much extra effort to delete filtered rows. Jeff "GG" wrote: After highlighting the rows hit F5...special...visible cells only...Ok. then rightclick with the selection on and chose delete. "JDOlson" wrote: I recently switched from Excel 2003 to Excel 2007. The process described below worked in Excel 2003, but has not worked for me in Excel 2007. After filtering records, I highlight the rows I want to delete, then "right-click" and "delete rows." In addition to the highlighted rows being deleted, the non-filtered (hidden) rows which are found after my first highlighted row, and before the last hightlighted row, are also deleted. For example, assume there are 50 populated rows. Using autofilter, rows 5, 17 and 20 are visible. I highlight rows 5, 17 and 20, then delete these rows. The result is that rows 5 through 20 are all deleted, not just rows 5, 17 and 20. Is there a default setting in Excel 2007 that controls for this change? I am using Micorsoft Office Excel 20070 (12.0.6300.5000) SP1 MSO (12.0.6213.1000) Thanks! Jeff "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Permanently delete filtered records
Ron and All,
I've noticed another quirk regarding working with filters in Excel 2007. If, for example, I apply a filter to column "G", then copy or paste or delete cells in another column, the "copy, paste and/or delete" function is applied to data in hidden rows. When I "copy, paste and/or delete" data in the column that has a filter applied, the data in hidden rows is not affected. I just started working in Excel 2007 about 2 weeks ago. This behavior is very different from what I observed when working in Excel 2003. Jeff Olson "JDOlson" wrote: Thanks for the input, Ron. My version of Excel 2007 does not have an "Edit" dropdown menu. I've tried "right-clicking" the rows as you describe, but the hidden rows are also deleted. Do you have any other suggestions? Jeff "JDOlson" wrote: I recently switched from Excel 2003 to Excel 2007. The process described below worked in Excel 2003, but has not worked for me in Excel 2007. After filtering records, I highlight the rows I want to delete, then "right-click" and "delete rows." In addition to the highlighted rows being deleted, the non-filtered (hidden) rows which are found after my first highlighted row, and before the last hightlighted row, are also deleted. For example, assume there are 50 populated rows. Using autofilter, rows 5, 17 and 20 are visible. I highlight rows 5, 17 and 20, then delete these rows. The result is that rows 5 through 20 are all deleted, not just rows 5, 17 and 20. Is there a default setting in Excel 2007 that controls for this change? I am using Micorsoft Office Excel 20070 (12.0.6300.5000) SP1 MSO (12.0.6213.1000) Thanks! Jeff "Ron Coderre" wrote: After you filter the records (using either AutoFilter or Advanced Filter) Select from the first filtered record down through the last (Do not select the data headings) Then, from the Excel main menu: <edit<delete....your only option will be to delete entire rows. Click [OK] and only the visible filtered rows will be deleted. <data<filter<show all will redisplay the hidden rows Does that help? *********** Regards, Ron XL2002, WinXP "Allen" wrote: I want to filter records in excel and then permanently delete the records that have been filtered out. Any ideas? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPOST: Count certain records in filtered data | Excel Discussion (Misc queries) | |||
How do I delete duplicate records from an entire Excel workbook? | Excel Discussion (Misc queries) | |||
Excel - How do I delete filtered out rows? | Excel Discussion (Misc queries) | |||
Delete record(s) in other cells based on value of one cell. | Excel Discussion (Misc queries) | |||
How do I delete filtered lines in Excel 2000? | Excel Worksheet Functions |