MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sorting array formulas

Posted by David on August 02, 2001 1:51 PM

I have an array formula applied to several thousand rows, where the result is mostly zeros, with a few non-zeros strew about.
I only want to keep the rows with a non-zero result, but there are too many rows to look through individually and pick out the
ones I want. When I try to sort the whole worksheet by that column, I get an error message that says:
"Can not change part of an array."
So, how can I sort my worksheet?

Posted by Cory on August 02, 2001 2:34 PM

Have you tried using the autofilter (Data --> Filter --> AutoFilter) and custom filtering the 0s out?
If not then activate the auto filter, click on the down-arrow for the column you want to sort by, choose "custom", then choose "not equal" and 0. This should filter out the zeros. To actually sort the data, copy and paste it (with the filter still on) to a new sheet. From there you can sort it how you want.

If that was too unclear, feel free to ask again...