Search code examples
excelvbaautofill

How to use VBA to Autofill a Row until the end of the number of data in another row

I have a macro that fills a range (A2:B110) based on data that is available in column C. However column C is growing daily and my macro will fill only until B110.

How can I make this B110 as a variable based on column C size?

Currently this is my code:

Range("A2:B2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2:B110")
Range("A2:B110").Select

Thank you very much.

Solution

  • Use the .SpecialCells Property. For instance,

    Dim lcol As Long
lcol = Range("C:C").SpecialCells(xlCellTypeLastCell).Row

    This finds the last cell with a value in Column C. Then, you can set the destination range like

    Selection.AutoFill Destination:=Range("A2:B" & lcol)