Search code examples
excelvbarangeautofill

Control range in VBA module when using AutoFill

I have a custom VBA function in Excel using two ranges

Function JOIN(r1 As Range, r2 As Range) As String

I copy this function using AutoFill. I want the first range to be fixed (it's a header row) and the second range to be dynamic. How can that be done?

Solution

  • Just use relative/absolute references when using your function like you'd do with other functions/formulas:

    $A$1 = Headercell A1 will remain the one you want for r1
    A$1 = If you want to drag/autofill to the side as well
    $A1 = If you want the column to remain the same but not the row
    A1 = everything changes depending on where you drag it to

    See documentation

    In your case: =JOIN($A$1;A2) would be a use of your function

    Could also go to row 1 of r1 but that's more of a hassle since headers aren't always on the first row.