VBA to Copy to Last Row for a Single Row

    Hello - I have a macro that determines the last populated row in a region and fills down the formula to the last row. It works awesomely unless there is only ONE row populated, in which case it copies down the row above. Here is the code:[INDENT]
    Sub CopyDown()


    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
    Range("O14").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Key!C[-4]:C[-3],2,FALSE)"
    Range("O14").Select
    Range("O14:O" & LastRow).FillDown


    End Sub[/INDENT]


    I have this code for multiple columns, etc, but they're all using the same "fill down" information.


    I'm one of those "create a macro by recording it, then edit it" people and I'm slowly teaching myself how to write it, but not there yet. Any help would be greatly appreciated!

    Re: VBA to Copy to Last Row for a Single Row


    Can you elaborate on what you mean by "only ONE row populated". Is row 14 always have data, or can this change? If row 14 always has data, you could at least shorten your macro like this:

    Code
    Sub CopyDown()




Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Range("O14:O" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],Key!C[-4]:C[-3],2,FALSE)"
End Sub


    There's no need to do all that selecting, as XL VBA will let us edit the objects (cell ranges) directly. :)

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

    Re: VBA to Copy to Last Row for a Single Row


    Thank you, Luke. Row 14 will always have something in it. But it may not go past that row. I will give this a shot!

    Re: VBA to Copy to Last Row for a Single Row


    Glad it worked, thanks for the feedback!

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login