Forum Discussion

qazzzlyt's avatar
qazzzlyt
Copper Contributor
Feb 04, 2023

VBA fill formula of non consecutive range

Suppose I have formula in A1

I can do Range(“A1:A10”).FillDown

But how about if I want Excel fill formula of A10 ONLY? (bypass A2:A9)

Of course copy paste is a way. Any method beside copy paste?

thanks in advance

excel
Formulas and Functions
Macros and VBA

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 04, 2023

    qazzzlyt 

    If the cell/range references in the formula in A1 have only absolute row references (i.e. with $ before the row numbers), you can use

     

    Range("A10").Formula = Range("A1").Formula

     

    But if there are relative row references (without $ before the row numbers, that won't work). Copy / Paste or Copy / Paste Formulas is the way to go:

     

    Range("A1").Copy Destination:=Range("A10")

     

    or

     

    Range("A1").Copy

    Range("A10").PasteSpecial Paste:=xlPasteFormulas

    • qazzzlyt's avatar
      qazzzlyt
      Copper Contributor
      Feb 05, 2023
      yes there are relative reference. I’ll have to use copy then. Thanks!

Resources