Flip sign in VBA for hotkey - keep formatting, skip non-numeric values

[CountingUpToN]

I've been looking for some time for code to perform the simple operation of flipping numbers back and forth between positive and negative. As an accountant I've tried many, many versions. Some work quickly, but balk when it interacts with text - error #Value . Others are super slow as it tests first for numeric values and skips the ones that have text - timing out when it reaches tens of thousands of data points. Please help me. I know that the fast way is to put a -1 in a cell, copy, p-s-multiply but that's a lot of key strokes. I run the keyboard like no one else I've seen, so one quick bar hot key, and I am super happy.

 

[Rick Rothstein]

Does this macro work for you...

Sub FlipSign()
  Dim Ar As Range
  For Each Ar In Selection.SpecialCells(xlConstants, xlNumbers)
    Ar.Value = -Ar.Value
  Next
End Sub

 

[CountingUpToN]

You. Are. Amazing. Works so fast and leaves the formatting. Grateful for your knowledge shared.

 

[CountingUpToN]

Oh, no. I have to revise and extend my remarks about your greatness. So sad. The thing is the code is behaving just like the code I had for the If-Then statement. Since the test statement is now For Each, the code takes more than five minutes to flip 5,000 values. Back to the drawing board.

 

[Rick Rothstein]

Oh, no. I have to revise and extend my remarks about your greatness. So sad. The thing is the code is behaving just like the code I had for the If-Then statement. Since the test statement is now For Each, the code takes more than five minutes to flip 5,000 values. Back to the drawing board.

See if this modification makes it any faster...

Sub FlipSign()
  Dim Ar As Range
  For Each Ar In Selection.SpecialCells(xlConstants, xlNumbers).Areas
    Ar = Evaluate("-" & Ar.Address)
  Next
End Sub

 

[CountingUpToN]

Just ran with it with almost 19 thousand rows and it was a blink of an eye. Amazing.