Select non-consecutive cells for deletion
 

I am trying to filter a large range of cells in a column for several
different values. If the values exist, the entire row should be deleted.
The problem is, across many cells in the range, if each cells is evaluated
and deleted one by one, the process can be very slow. I want to figure a way
to some how select or store all the cells in the range first and then delete
them in one go. How/is this possible? (Note: Since there are a number of
values that trigger the deletion, I don't think autofiltering is an option)

Select non-consecutive cells for deletion
 

1. Select the column whose value will determine whether a row should be
deleted.
2. EditReplace to replace a value to be deleted by an unusual string, e.g.
"zzzzzzzzzz"
3. Repeat 2 until all the values to be deleted have been replaced by
zzzzzzzzzz.
4. Filter the rows with zzzzzzzzzz.
5. Delete all those rows.

"Mark" wrote in message
...
I am trying to filter a large range of cells in a column for several
different values. If the values exist, the entire row should be deleted.
The problem is, across many cells in the range, if each cells is evaluated
and deleted one by one, the process can be very slow. I want to figure a
way
to some how select or store all the cells in the range first and then
delete
them in one go. How/is this possible? (Note: Since there are a number of
values that trigger the deletion, I don't think autofiltering is an
option)

Select non-consecutive cells for deletion
 

Have you turned off screen updating?
(Application.ScreenUpdating = false)

Szalapsk

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...fo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=46647

Select non-consecutive cells for deletion
 

That's a unique solution that seems like it should fit my situation quite well.

"Shatin" wrote:

1. Select the column whose value will determine whether a row should be
deleted.
2. EditReplace to replace a value to be deleted by an unusual string, e.g.
"zzzzzzzzzz"
3. Repeat 2 until all the values to be deleted have been replaced by
zzzzzzzzzz.
4. Filter the rows with zzzzzzzzzz.
5. Delete all those rows.

"Mark" wrote in message
...
I am trying to filter a large range of cells in a column for several
different values. If the values exist, the entire row should be deleted.
The problem is, across many cells in the range, if each cells is evaluated
and deleted one by one, the process can be very slow. I want to figure a
way
to some how select or store all the cells in the range first and then
delete
them in one go. How/is this possible? (Note: Since there are a number of
values that trigger the deletion, I don't think autofiltering is an
option)

Select non-consecutive cells for deletion
 

You could even a modification of Shatin's suggestion...

Insert another column. Build a formula that returns true or false depending on
one of those values to be deleted is found in the cell of the same row.

Then filter on that.

But this depends on the type of data that should be deleted--and whether you can
find a pattern to make into a formula.

Mark wrote:

That's a unique solution that seems like it should fit my situation quite well.

"Shatin" wrote:

1. Select the column whose value will determine whether a row should be
deleted.
2. EditReplace to replace a value to be deleted by an unusual string, e.g.
"zzzzzzzzzz"
3. Repeat 2 until all the values to be deleted have been replaced by
zzzzzzzzzz.
4. Filter the rows with zzzzzzzzzz.
5. Delete all those rows.

"Mark" wrote in message
...
I am trying to filter a large range of cells in a column for several
different values. If the values exist, the entire row should be deleted.
The problem is, across many cells in the range, if each cells is evaluated
and deleted one by one, the process can be very slow. I want to figure a
way
to some how select or store all the cells in the range first and then
delete
them in one go. How/is this possible? (Note: Since there are a number of
values that trigger the deletion, I don't think autofiltering is an
option)




--

Dave Peterson