Paste Special on Filtered Data

MMGroup

Board Regular
Joined
Jul 10, 2002
Messages
50
Excel 2003, SP2, Windows XP
Setup: Two columns, as follows:
Pet Cost
Dog
Cat
Dog

Filter on Pet = Dog
Somewhere convenient, enter this formula: =5/2
With filter on, copy the formula and paste special, values into the Cost column
Turn the filter off
The cost column for Cat has been overwritten.
Whyzat?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi MMGroup

It doesn't matter if the filter is on or off as far as the paste is concerned. The command paste works on the selected area, filtered or not. You are selecting both visible and non-visible cells. What you have to do is to select correctly the range.

After you filter on pet, select the range where you want to paste the values as you did before, but before the paste do: Edit>Goto, click on Special and check Visible cells only. Now the paste should work as you want.

Hope this helps
PGC
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
There's a funky "Select Visible Cells" toolbar button in the Edit menu from View>Toolbars>Customize which you might find useful if you have to do this a lot.

Richard
 

MMGroup

Board Regular
Joined
Jul 10, 2002
Messages
50
Thanks! (Added detailed instructions)

Thanks for the tip. Here is the detailed setup.
View | Toolbars | Customize
Command Tab
Category = Edit
Click Select Visible Cells
Drag this button to any of the visible toolbars
Close this window

Setup the spreadsheet as above, filter on dogs
Select the cell with the formula, then copy
Select the two visible cells
Click on the Select Visible Cells button on the toolbar (wherever you put it)
Right-click, select Paste Special, Values
The cat row will not update.

I think this is a bug in Excel for this reason:
Do the same setup (so the first column is filtered on dogs), go to the formula cell, copy, then paste (not paste special). The formula is not copied to the hidden cell. I guess it makes sense either way, but it does not make sense both ways.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
Keyboard shortcut for selecting visible cells: highlight range and press Alt and ; at the same time.
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top