Clean-up Code

[Howard]

This code works, but how can I clean it up to be a little more efficient. I'm
just trying to learn the code and the technique on a small scale before I
apply it to the real project.

Just multiplying one column by another, then Copy>PasteSpecial>Values.

Dim Row As Long

Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Formula = "=RC[-2]*RC[-1]"
Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Copy
Range(Cells(1, 3), Cells(Row, 3)).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Thanks,


--Howard

 

[Don Guillett]

Sub OneWay()
dim i as long
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
Cells(i, 3).Value = Cells(i, 2) * Cells(i, 3)
Next i
End Sub

 

[ryguy7272]

Your code seems pretty efficient! I just recorded two simple macros, and
added just a bit of code after stopping the Macro Recorder, to demonstrate a
few alternate ways of doing what (I think) you are doing:

Sub Macro1()
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C10")
End Sub

Sub Macro2()
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End Sub

Regards,
Ryan--

 

[JLGWhiz]

Hi Don, shouldn't this line:
Cells(i, 3).Value = Cells(i, 2) * Cells(i, 3)
Be:
Cells(i, 3).Value = Cells(i, 1) * Cells(i, 2)
?
Howard's use of CurrentRegion to get the row count can be misleading. As
long as the reference cell is adjacent to a data range, Excel will use the
data range as the current region of the reference cell, even if the column of
the reference cell is empty.

Sub OneWay()
dim i as long
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
Cells(i, 3).Value = Cells(i, 2) * Cells(i, 3)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

This code works, but how can I clean it up to be a little more efficient.
I'm
just trying to learn the code and the technique on a small scale before I
apply it to the real project.

Just multiplying one column by another, then Copy>PasteSpecial>Values.

Dim Row As Long

Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Formula = "=RC[-2]*RC[-1]"
Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Copy
Range(Cells(1, 3), Cells(Row, 3)).PasteSpecial
Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Thanks,


--Howard

 

[Howard]

Thanks to all of you. Certainly gives me a lot to think about!
--
Howard

Hi Don, shouldn't this line:
Cells(i, 3).Value = Cells(i, 2) * Cells(i, 3)
Be:
Cells(i, 3).Value = Cells(i, 1) * Cells(i, 2)
?
Howard's use of CurrentRegion to get the row count can be misleading. As
long as the reference cell is adjacent to a data range, Excel will use the
data range as the current region of the reference cell, even if the column of
the reference cell is empty.

Sub OneWay()
dim i as long
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
Cells(i, 3).Value = Cells(i, 2) * Cells(i, 3)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

This code works, but how can I clean it up to be a little more efficient.
I'm
just trying to learn the code and the technique on a small scale before I
apply it to the real project.

Just multiplying one column by another, then Copy>PasteSpecial>Values.

Dim Row As Long

Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Formula = "=RC[-2]*RC[-1]"
Row = Cells(1, 3).CurrentRegion.Rows.Count
Range(Cells(1, 3), Cells(Row, 3)).Copy
Range(Cells(1, 3), Cells(Row, 3)).PasteSpecial
Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Thanks,


--Howard