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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,801
Office Version
365
Platform
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,801
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,819
Messages
5,513,584
Members
408,960
Latest member
dichthuatco

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top