Permanently delete filtered records

[Guest]

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[Guest]

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

 

[Roger Govier]

Hi Allen


Work on a Copy of your data - just in case!!
Data>Filter>Autofilter
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
Click>Delete

Data>Filter>Show All
and you will be left with just the rows you wanted to retain.

 

[Guest]

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[Guest]

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[Dave Peterson]

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.

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[Roger Govier]

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

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

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

I want to filter records in excel and then permanently delete the
records
that have been filtered out. Any ideas? Thanks.

 

[Guest]

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

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

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

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


:

I want to filter records in excel and then permanently delete the
records
that have been filtered out. Any ideas? Thanks.

 

[JDOlson]

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[GG]

After highlighting the rows hit F5...special...visible cells only...Ok. then
rightclick with the selection on and chose delete.

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[JDOlson]

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

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

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

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[JDOlson]

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

After highlighting the rows hit F5...special...visible cells only...Ok. then
rightclick with the selection on and chose delete.

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

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


:

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.

 

[JDOlson]

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

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

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

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


:

I want to filter records in excel and then permanently delete the records
that have been filtered out. Any ideas? Thanks.