Solved: Fastest way to divide all rows in column C by a fixed number, put results in column D

[NukedWhale]

I have a simple calculation that I need to perform MANY times (upwards of 30-40K).

I have whole numbers in rows (30-40k rows) of column C, the numbers can be anything from 0 to 20k.

I need to divide that number by a number stored in another variable (MyRows). This number will be a whole number between 0 and 40 k. I want the value result of that division in column d.

So if:

C1=10 and MyRows=100 then D1=0.1
C2=20 and MyRows=100 then D2=0.2

Example data layout attached. Let me know if you have any questions or need clarification.

Thanks

[Bob Phillips]

[vba]

Dim LastRow As Long
Dim tmp As Variant

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
tmp = Range("IV1").Value
Range("IV1").Value = myrows
Range("IV1").Copy
Range("C1").Resize(LastRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
Range("IV1").Value = tmp
Application.CutCopyMode = False
[/vba]

[Simon Lloyd]

I believe the fastest way as you asked would be to put your division formula in column D select it then Ctrl+Shift+End then EDIT>FILL>DOWN

EDIT: If you have a variable stored you could create a UDF and do the same.

[NukedWhale]

xld,

The division is correct and it's fast. My version would have been a slow and clunky loop.

I'm having one minor issue. Currently it pastes over column C. How do I paste/post the results of the division in D, while preserving the original values in column C?

[Bob Phillips]

[vba]

Dim LastRow As Long
Dim tmp As Variant

LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Columns("C").Copy Columns("D")
With Range("IV1")

tmp = .Value
.Value = MyRows
.Copy
Range("D1").Resize(LastRow).PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide
.Value = tmp
End With
Application.CutCopyMode = False
[/vba]

[NukedWhale]

Nailed it