VBA: Paste Special to Visible Cells

[Ed1]

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.

 

[Jon von der Heyden]

Something like:

Range("A1").Copy
Range("A2:A100").[B]SpecialCells(xlCellTypeVisible)[/B].PasteSpecial xlValues

 

[Ed1]

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]

Sorry, mistake, it should be:

Range("A1").Copy
Range("A2:A100").PasteSpecial Paste:= xlPasteValues, Operation:= xlPasteSpecialOperationMultiply

 

[Ed1]

Jon, thank you so much for your help - much appreciated.

Ed.