How to copy/paste value on filtered cells

[Bob L]

Hi all,

I was wondering how to copy/paste value on filtered cells. In the example attached, you see that rows 5 and 6 are filtered out and a simple vlookup formula in column B. I'd like to copy/paste value only the visible description (vlookup formula) here.

Thanks.

 

[sijpie]

Does normal copy paste as values not work?

 

[Bob L]

No, when you select B2:B7 and try to copy/paste value, you'll get this message:

 

[Joe4]

When pasting, you typically only need to select the first cell in the range that you want to paste to.
Excel is smart enough to fill on the other cells if your copy range is more than one cell.

 

[Bob L]

When pasting, you typically only need to select the first cell in the range that you want to paste to.
Excel is smart enough to fill on the other cells if your copy range is more than one cell.

If you meant select cells B2:B7, copy, go on cell B2 and paste value, I get this error:

 

[Joe4]

If you are using Excel 365 (which your profile seems to suggest), a better option may be to use the FILTER function, and just set the function to use the same condition that your filter is.
See: Excel FILTER Function Examples - Lists Update Automatically

 

[sijpie]

Have you got any merged cells in the area where you are pasting to?

 

[Bob L]

Have you got any merged cells in the area where you are pasting to?

No.