Macro to Auto Fill Down to last adjacent cell

Question

I am trying to use macro recorder in Excel to record a macro to fill down a column of cells, however because the fill down each time is a different number of cells it either fills down to short or too long and this seems to be because the macro identifies the cell range and its fixed.

Is there anyway that I can get it to fill down to the last populated neighbouring cell. E.g. AutoFill down column E until it reaches the last populated row in column D. I have looked at some examples on here but the code all looks very different so not sure if it can be done with macro recorder or I have to get someone to write some code or is it something that has to be done manually?

This is the code that I have in the macro.

ActiveCell.FormulaR1C1 = _
        "=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
    Selection.AutoFill Destination:=Range("E2:E1344")
    'Selection.AutoFill Destination:=Range("E2:E1344")
    Range("E2:E1344").Select

If anyone can help i'd be extremely grateful

Answer 1

This example shows you how to fill column B based on the the volume of data in Column A. Adjust "A1" accordingly to your needs. It will fill in column B based on the formula in B1.

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

Answer 2

Untested....but should work.

Dim lastrow as long

lastrow = range("D65000").end(xlup).Row

ActiveCell.FormulaR1C1 = _
        "=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
    Selection.AutoFill Destination:=Range("E2:E" & lastrow)
    'Selection.AutoFill Destination:=Range("E2:E"& lastrow)
    Range("E2:E1344").Select

Only exception being are you sure your Autofill code is perfect...

Answer 3

ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown