Apply divide formula to entire row in macro on Excel?

Question

I am tring to fix a problem.

Like Copy / Special Pasta - divide, i would like to do it in VBA.

For example, I have numbers from A to AA.

I want to divide the range A5:AA5 by 2 completely. It’s like a special paste, divide the values.

It will continue like this until it reaches an empty cell in A6:AA6 / 2, A7:AA7 / 2, and so on.

But i dont want to make each cells divide or Sum of divide, like selecting all C2:H2 and divide by 2.

Anyone can help?

Answer 1

One way to do it is using the PasteSpecial command. The trick is to put the number you want to divide by in a cell, copy it, .PasteSpecial, and then remove the data from that cell. Something like this:

Range("C9").Value = 2    
Range("C9").Copy
Range("A5:F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
Range("C9").ClearContents

Obviously there are various ways to add data to a cell, delete data, etc. But I wanted to give you a quick example that you can modify to fit your sheet's layout.

If you want to use a loop, here is a way:

Sub test2()
Dim x As Long

Dim rng As Range
Dim c As Range

x = 2

Set rng = Range("A5:F5")

For Each c In rng
    c.Value = c.Value / x
Next c

End Sub

Someone smarter than me will have to chime in w/ @BigBen's suggestion of using an array.

Answer 2

Here's an example using an array:

Dim arr() As Variant
arr = Range("A5:AA5").Value

Dim i As Long, j As Long
For i = LBound(arr, 1) To Ubound(arr, 1)
    For j = Lbound(arr, 2) To Ubound(arr, 2)
        arr(i, j) = arr(i, j) / 2
    Next
Next

Range("A5:AA5").Value = arr