title: How to copy down formula in Excel to last cell with data in VBA
body: <p>I have a code that will perform a formula and then place the value in Column G and then sort column G... The code works fine but I will be having a dynamically changed amount of row I need the formula to be ran for so I am trying to figure out how to change the range from just certain cells to all cells that have a value. Any help is appreciated. The code I have written is below.</p>
  
  <pre><code>Sub Sorting()
  Worksheets.Select ("FedEx Air Ops Workbench Report")
  Range("G4").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],BUTTONS!R2C11:R6C12,2,FALSE)"
  Selection.AutoFill Destination:=Range("G4:G58"), Type:=xlFillDefault
  ActiveWorkbook.Worksheets("FedEx Air Ops Workbench Report").Sort.SortFields. _
      Add Key:=Range("G4:G58"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("FedEx Air Ops Workbench Report").Sort
      .SetRange Range("A4:G58")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
  </code></pre>
  
  <p>End Sub</p>