Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deletion Of Cells | Excel Worksheet Functions | |||
Auto Deletion of 4 consecutive rows out of every 5 | Excel Programming | |||
Auto Deletion of 4 consecutive rows out of every 5 | Excel Programming | |||
Select non-consecutive rows | Excel Discussion (Misc queries) | |||
Select two consecutive rows | Excel Programming |