0

I have recorded a macro to fill down a column of cells after running a proper formula, however, because the fill down each time is a different number of cells it either fills down to short or too long. I'm not sure how to set an open-ended range as you can see with my arbitrary "A1:A200" range. Can you please help me and explain the steps so that I can start to learn how to do these things on my own? Below is my macro code. Any help with this would be greatly appreciated. Thank you.

Sub Proper()
'
' Proper Macro
'

'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A200")
ActiveCell.Range("A1:A200").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Nickel17
  • 1
  • 2
  • There are several question about [finding the last row of a range](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) that should prove useful. And I know the macro recorder does this all the time, but there are [good reasons to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Dec 18 '19 at 16:06
  • 1
    You can enter the formula in the entire range in one go. No need to autofill. `Range("A1:A" & LastRow).Formula = YourFormula` You can find the last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Dec 18 '19 at 16:09

0 Answers0