Autofill destination creating longer runtime

Question

I have used the following macro to convert text to proper format. I select a cell and run the macro. Suppose column A contains 3 filled rows(say A1, A2 and A3 and I want to apply the formula PROPER to columns B1, B2 and B3, B1 being my active cell. When, I run the macro it takes a long time to autofill since while selecting B1 to B3, the number of rows the macro considers is till the end of the worksheet instead of the number of rows in column A. Please suggest how can I solve this issue regarding the Autofill destination.

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
    ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
    ActiveCell.Select
    **Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))**
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

How come you're using a macro? Why not just enter in the cells in column B directly? — BruceWayne

BruceWayne BruceWayne · Accepted Answer · 2015-08-28T20:40:46

Assuming your data starts in A1, and you want to put your formulas in column B, you can use the following (note the removal of .Select)

Sub macro7()
Dim lastRow as integer
lastRow = Activesheet.usedrange.rows.count
Range("B1").Formular1c1 = "=Proper(RC[-1])"
Range("B1").AutoFill Destination:=Range(Cells(1,2),Cells(lastRow,2))
Range(Cells(1,2),Cells(lastRow,2)).Copy
Range(Cells(1,2),Cells(lastRow,2)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range(Cells(1,1),cells(lastRow,1)).ClearContents
End sub

But, as asked above, why do you want this as a macro? Unless there's going to be more added, this is probably quicker and easier to do just using =Proper(a1) in the cells, and autofilling down without VBA.

Autofill destination creating longer runtime

1 Answers