Can Somebody Help With This?? Excel 2010 Bug - Copy/Paste While Filtered

[jwhitten]

Hello,

I am using Excel 2010 Professional (v 14.0.5128.5000 32-bit) and ran into what seems to me like a bug...

I have put together a large spreadsheet of systems and network devices along with columns containing various properties. I have been working on filling in the various properties and to help select the right rows, I have been using the "Filter" function. I use the filter to drill down to the rows containing the column I want to modify-- which will contain the exact same value for all rows selected. Then I edit the value of one row/column, then select it, copy it, and then drag-select all of the remaining rows and do a paste-value.

That should be it... except it isn't, ALL of the rows *inbetween* the filtered rows, ALSO get updated with that value, as if they had not been filtered at all !!! The only rows that do NOT get updated are the rows which lie ENTIRELY OUTSIDE the filtered range.

If this is not a bug but instead a "feature"-- someone please clue me in as to its supposed purpose ! :-)

Thanks,



John Whitten

[Richard Buttrey]

Hello John,

Rather than Copy it, selecting all rows and paste valuing it, just select the changed cell and drag it down.

Alternatively use Ctrl-C to copy it, hold the shift key down and do an End Down arrow to highlight all filtered cells and the Ctrl-V.

I don't think it's a bug, just the way it's meant to work. Otherwise with a filtered range there would be no way of changing all rows including filtered rows.

As you can see there are two options, one changes all rows the other only filtered rows.

Regards

[jwhitten]

Hello John,

Rather than Copy it, selecting all rows and paste valuing it, just select the changed cell and drag it down.

Hi Richard,

Thanks for the reply. I have tried the above with the same results.

Alternatively use Ctrl-C to copy it, hold the shift key down and do an End Down arrow to highlight all filtered cells and the Ctrl-V.

I have not tried this, but will in the morning. However, even if it does work (and I hope it will), it still seems like a broken interface (or else coded in a silly manner).

I don't think it's a bug, just the way it's meant to work. Otherwise with a filtered range there would be no way of changing all rows including filtered rows.

As you can see there are two options, one changes all rows the other only filtered rows.

Regards

If this behavior happens by design then I think it is coded in a very silly manner. When you use the filtering mechanics, the goal is to weed out the rows you *don't* want (or alternately, to select the ones you *do* want). Either way, the goal is to end up with a collection of rows that you can then do something with, or to. For it to *then* have the *un-obvious* behavior to interact with rows that are *not* selected and are for all intents and purposes invisible / non-existent, seems like a very silly idea to me.

Thank you very much for your response, I will certainly give your suggestion a try.

John

[martindwilson]

try selecting visible cells only

[Richard Buttrey]

Hi,

I understand the point you're getting at.

It may be aligning Excel functionality with VBA. There we have the .SpecialCells(xlCellTypeVisible) syntax. That operates on only filtered rows whereas without the xlCellTypeVisible bit the same instruction will affect all cells in a range.

Not a definitive answer I know, but the best I can come up with at the moment.

Regards

[broro183]

hi everyone

To add to Martin & Richard's last responses, and assuming that excel 2010 hasn't changed the rules that excel 2007 works with, you can copy to visible cells by:
- press [ctrl + c] on your desired range
- selecting the filtered range,
- press [alt + ;] to select the visible cells
(or by pressing [F5], [alt + s], choosing the appropriate option, pressing [y] and then hitting [enter]).
- then use [ctrl + v] to paste.

Alternatively, if you are only modifying a single column (or you are happy to "rinse & repeat") then you can enter the value directly into the relevant cells without copying by:
- selecting the cell in the first visible row of the column you want to change,
- pressing [ctrl + shift + down arrow] to select the section in the column,
- pressing [alt + ;] for visible cells,
- (if necessary, deleting &...) then typing in your value
- and finally...
pressing [ctrl + enter] to complete the cell entry (instead of just [enter]) to update all the selected cells in one go.

In case you haven't guessed yet, I like keyboard shortcuts ;-)

hth
Rob

[jwhitten]

Thanks everybody for the quick replies! I had this question up on the MS office forum for over a month without a single response.... I put it up here a little while ago and already have some great responses. Even if I can't figure out a good solution, it's at least nice to know I have some company :-)

Thanks!

John

[nandkishorskale]

it is NOT working. can someone help on this asap?

[Richard Buttrey]

it is NOT working. can someone help on this asap?

@ nandkishorskale. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads (and this one is well over two years old) are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.