Changing the sign of numeric values in a column

Question

I have data in column D.

There is a header in column D1 and numeric values in D2 downward. I would like to select all numeric values in column D (the number of values is unknown) and multiply them by -1 (replacing the current values in column D). How would I do this through VBA code?

If I could use formulas in Excel I would simply drag the formula D2*-1 downward; however, I need the VBA equivalent.

Answer 1

The following works almost instantaneously when tested with 100,000 random values:

Sub MultColDbyOne()
    Dim i As Long, n As Long, A As Variant
    n = Cells(Rows.Count, "D").End(xlUp).Row
    A = Range(Cells(2, "D"), Cells(n, "D")).Value
    For i = LBound(A) To UBound(A)
        A(i, 1) = -A(i, 1)
    Next i
    Range(Cells(2, "D"), Cells(n, "D")).Value = A
End Sub

The sub works by first determining the last row with data in column D, then transferring it to a VBA array (which is, somewhat annoyingly, a 2-dimensional array with only 1 column), looping through that array replacing each number in it by its negative, then transferring it back. This Range to array then back to Range strategy is fairly common (and fairly efficient) in VBA.