0

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Marco Terra
  • 3
  • 1
  • 4
  • Find the last row and store it to a variable. – findwindow Jun 21 '22 at 19:30
  • 1
    `Select` is a macro-recorder thing and is [best avoided](https://stackoverflow.com/a/10717999). `Application.CutCopyMode = False` is irrelevant here. There may be a more efficient or easier way to fill the range than using `AutoFill`, so why don't you share the formulas in `A2:B2`? The most common way, probably the easiest, to get the last row in a column is by using something like `Dim lRow As Long: lRow = Cells(Rows.Count, "C").End(xlUp).Row`. Read about it in more detail [here](https://stackoverflow.com/a/11169920). Why don't you share the complete code and explain what it needs to do? – VBasic2008 Jun 22 '22 at 08:42

2 Answers2

0

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)
OGP99
  • 149
  • 9
0

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.

Bryan
  • 48
  • 3