Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Excel 2007 copy visible cell only is only thru for few number
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. -- H. Frank Situmorang |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Excel 2007 copy visible cell only is only thru for few number
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. "Frank Situmorang" wrote: 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. -- H. Frank Situmorang |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - cell borders not visible when cells colored | Excel Discussion (Misc queries) | |||
Excel 2007 visible Cell | Excel Discussion (Misc queries) | |||
Number of visible Tick Labels limited to 100 in Excel 2007 | Charts and Charting in Excel | |||
How to obtain the Row number of next visible cell Down? | Excel Programming | |||
Copy to visible cell after Autofilter | Excel Discussion (Misc queries) |