Copy & Paste Special Mutliply (Excel 2010)

Topic

New Reply

Hello,

I’m trying to write a bit of code that would simply multiply a range by -1 WITHOUT having to actually put the “-1” in the Excel worksheet in cell AA1. How can I just define the “-1” as a value or integer in the code, then “copy” the “-1” so the last two lines of code will work (in other words so the copy pastespecial multiply will work).

Thanks so much!
Lana

Range(“AA1”) = “-1”

Range(“AA1”).Select
Selection.Copy

Range(“E2:E” & Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

Reply | Quote

Replies

See: http://www.vbaexpress.com/kb/getarticle.php?kb_id=47

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Try this

Code:

Dim rng As Range
Set rng = Range(“E2:E” & Cells(Rows.Count, 1).End(xlUp).Row)
rng = Evaluate(“=” & rng.Address & “*-1”)

Are you sure the range being selected by .End(xlUp) is what you expected? A shorter way would simply define it as Range(“E1:E2”)

Reply | Quote