how to avoid using loop in this case

[lubbamkt]

Hi all,

I am wondering if there are any ways out to do the following task instead of using loop to search each cell. This is a bit slow. Thanks
An sample file is attached.
sample.xls

Please Login or Register  to view this content.

[zbor]

Here:

Please Login or Register  to view this content.

Just instead .Select of the range define that range as you want.

[nilem]

an option

Please Login or Register  to view this content.

[lubbamkt]

an option

Please Login or Register  to view this content.

thanks nilem, this is what i am looking for.

[kadeo]

an option

Please Login or Register  to view this content.

Here:

Please Login or Register  to view this content.

Just instead .Select of the range define that range as you want.

OP's original code checks the selected data and, based on the results there, clears the contents of positionally related cells in another sheet ("temp chart"). It searches through the selected data starting with the last column and moves in reverse direction (to the left) along the columns after each loop. The first column it reaches that does not meet the criteria, exits the loop.

So it could be the intended design of the procedure that only cells on the other sheet to the right of that column are cleared leaving all columns up to that column unaffected regardless of contents. Also, columns 1 to 9 of the selection are not even included in the loop so are never changed.

Your codes both remove data from the source data instead of from another sheet and they affect all of the data not just some of it.

That said, I'm having trouble coming up with a quicker way than this. I had thought about using ".cells.find" somehow until I saw the pattern of data removal.

Can OP clarify what the expected end result is or even provide a current working example?

[lubbamkt]

OP's original code checks the selected data and, based on the results there, clears the contents of positionally related cells in another sheet ("temp chart"). It searches through the selected data starting with the last column and moves in reverse direction (to the left) along the columns after each loop. The first column it reaches that does not meet the criteria, exits the loop.

So it could be the intended design of the procedure that only cells on the other sheet to the right of that column are cleared leaving all columns up to that column unaffected regardless of contents. Also, columns 1 to 9 of the selection are not even included in the loop so are never changed.

Your codes both remove data from the source data instead of from another sheet and they affect all of the data not just some of it.

That said, I'm having trouble coming up with a quicker way than this. I had thought about using ".cells.find" somehow until I saw the pattern of data removal.

Can OP clarify what the expected end result is or even provide a current working example?

kadeo, you are right, I just found an issue on the solution.

First of all, I am sorry to say all the data happened on the same sheet, so the sheets("temp chart") is also the sheet carrying the original data.
Second, I intend to do the check for all columns in the selection, not to exclude column 1 to 9.
Please see the updated code below

Please Login or Register  to view this content.

For nilem's solution, it deleted all the columns in the selection if they contain one "#N/A", but my intention is to delete the columns if both upper and lower cell contain "#N/A". Thus, this is not perfectly solve my issue. Is there anyone getting alternative solution? Thanks in advance

[nilem]

maybe so

Please Login or Register  to view this content.