VBA: Paste Special to Visible Cells

Ed1

New Member
Joined
Dec 25, 2009
Messages
6
Hi All,

I'm a relative VBA newbie, so please bear with me....

After learning the joys of looping in VBA, I have since realised (Translation: read online) that there are nearly always far more efficient ways of doing things than looping.

I have a macro that loops through all cells in column A and multiplies the values in column B by '-1' if it meets a specific criteria. I now realise it is far more efficient to copy the value '-1' then use the auto-filter, GoTo visible cells only, then paste special - multiply.

I can't figure out how to do this in VBA though (I assumed it would be fairly simple for some reason , so maybe I'm just missing something).

Is anyone able to point me in the right direction?

I ran a search on this site, but only came up with the following threads:

http://www.mrexcel.com/forum/showthread.php?t=482675&highlight=vba+visible+cells+paste+special

http://www.mrexcel.com/forum/showthread.php?t=85288&highlight=vba+visible+cells+paste+special

Thank you in advance.
 

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.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,874
Office Version
  1. 365
Platform
  1. Windows
Something like:
Code:
Range("A1").Copy
Range("A2:A100").[B]SpecialCells(xlCellTypeVisible)[/B].PasteSpecial xlValues
 

Ed1

New Member
Joined
Dec 25, 2009
Messages
6
Thanks for the very quick reply Jon.

Yep, I tried that (and I'm sure I've tried something similar recently), but it pastes the actual value into the visible cells (i.e. each cell becomes '-1') rather than multiplying them by '-1'.

Apologies if this is very simple question, but I just can't figure out how to multiply the visible cells by '-1' in VBA using paste special!
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,874
Office Version
  1. 365
Platform
  1. Windows
Sorry, mistake, it should be:
Code:
Range("A1").Copy
Range("A2:A100").PasteSpecial Paste:= xlPasteValues, Operation:= xlPasteSpecialOperationMultiply
 

Ed1

New Member
Joined
Dec 25, 2009
Messages
6
Jon, thank you so much for your help - much appreciated.

Ed.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,046
Messages
5,835,106
Members
430,343
Latest member
Sailingexcel

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