For Excel 2007 copy visible cell only is only thru for few number

[Frank Situmorang]

Hello,

I have tested that for Excel 2007, the copy visible cells only can work for
only fiew numbers of rows. My spreadsheet has more than 55 000 rows and when
I do filtering and I want to copy only visible cells, it can not works it
says it is too complex.

Is there anyway to solve this problem. I have to separeae all the 2006
Purchas Order lines since excell has only 65000 lines plus.

Thanks for any idea provided.

 

[ShaneDevenshire]

Hi Frank,

First if you are using Excel 2007 your spreadsheet has 1,048,576 rows unless
you are in compatibility mode.

Suppose your items are in column A, starting in A1. While they are filtered
enter the following formula in an adjacent column, here B:

In B1:

=SUBTOTAL(3,A1)

Select cell B1 and Double-click the fill handle. This copies the formula
all the way down. Turn off the filter and select the entire range. Sort on
column B. This will group all the blank cells together and the non-blank
ones together. Select and copy all the non-blank (column B) rows of data.
--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
search for life beyond earth.