Copy/Paste values with a filter on

jlax34

New Member
Joined
Feb 18, 2015
Messages
26
Is there a simple way to copy/paste values when you have a filter on? I often have formulas in spreadsheets I want to work with and want to slowly get rid of them as I'm working through a column. I will filter on the results on want and want a way to remove the formulas just from the visible cells at that time (without having to download an add in as that isn't an option at work).
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
Does your sheet have headers in row 1? A simple macro could do this for you unless by "add in" you mean that you don't want a macro.
 

jlax34

New Member
Joined
Feb 18, 2015
Messages
26
I was referring to the add ins that you can download like Text Tools and others. Just not an option at my company or it may solve the issue. As far as a macro working, I'm not sure I was looking for go that far and was curious if there happened to be something easy that I have somehow overlooked after all these years.

The sheet I have allocates a quantity based on a subtotal that changes with the filter. Most sheets where I run into this, I can just break the link (because its often a Vlookup), or remove the filter and copy/paste values on the whole column. In this case, removing the filter changes the value of the cell and isn't an option. Not sure its worth the effort to write a macro though...
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
A macro could simply copy only the visible data and paste only the values back to the same range thus deleting the formulas in that range. Let me know if you're interested in trying this approach and which ranges, rows or columns you want to copy/paste the values.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,213
Messages
5,594,872
Members
413,945
Latest member
V51773

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