Forum Discussion

GregGann's avatar
GregGann
Copper Contributor
Mar 02, 2022
Solved

How do I access #SPILL area from FILTER() result in VBA?

If I use FILTER() function cell $B$1, how do I target or select the entire spill range from VBA?   There is a range or table. It is outlined in blue. The blue outline updates dynamically with the n...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Mar 02, 2022
    Use the # character, so $B$1#
mtarler's avatar
mtarler
Silver Contributor
Mar 02, 2022
Use the # character, so $B$1#
  • GregGann's avatar
    GregGann
    Copper Contributor
    Mar 02, 2022
    I know that works the cells, but I didn't know I can use that in VBA
    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 02, 2022
      I tested it and it worked for me. For example I did this in the Immediate window where D1 has a spill function:
      print activesheet.range("d1#").rows.count
      and got 12
      • GregGann's avatar
        GregGann
        Copper Contributor
        Mar 02, 2022

        mtarler 

         

        I am having better success than I hope for. Thanks

         

        Range("B6:Q6").Select
Selection.Copy
Range("B6#").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        ,

         

Resources