Copy nonerror values from a column to new column, no blank cells in between

[farmerdave]

I have a column of values generated by a formula, some of which may be errors. I want to have a simple way of starting at the top row and reproducing the nonerror values from that column into an adjacent column, but without any blank cells between the new columns values.

Please Login or Register  to view this content.

This code chops out the errors, but runs really slowly. Im sure there must be an easier way, can someone help me improve it. I thought the "=IsNA()" command could be used somehow.

[Leith Ross]

Hello farmerdave,

You probably need to disable calculations. Try this version.

Please Login or Register  to view this content.

[Marzuk]

No, the easiest way to do this is NOT to delete the cell, but to clear the cells contents then sort. This will do it in a blink of an eye. Deleting is a very slow process and is to be avoided unless you have a specific reason to do so.

.ClearContents

Edit: btw if you are trying to preserve the original order, you can insert and autofill a series, clear the row number that you inserted and the cell next to it, then sort what is left.

This has the added benefit of not having to worry about changing calculation settings.

[farmerdave]

Thanks, will give these a try.