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

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.

CodePudding user response：

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)
Range("A2:B110").Select

CodePudding user response：

Try this:

Range("A2:B2").Select
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2:B"&CStr(LastRow))
Range("A2:B"&CStr(LastRow)).Select

It search for the last row in column C and uses the value to define what fill.