AutoFill visible cells in filtered rows

[ysk]

Hi,

I have followed several examples found here on the usage of SpecialCells(xlCellTypeVisible) to autofill visible cells in filtered rows.
However it keeps throwing back Run-time error '1004': AutoFill method of Range class failed.

The last row of code works without the SpecialCells and will autofill all cells including hidden ones. Please help


Dim LastRow As Long

'Get last row of data
LastRow = Range("B" & Rows.Count).End(xlUp).Row

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C[-10],'[Arista ItemsinBacklog - Template.xlsm]JSGBacklog'!C2:C16,15,0)"

Selection.AutoFill Destination:=Range(ActiveCell.Address & ":L" & LastRow).SpecialCells(xlCellTypeVisible)

 

[Peter_SSs]

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C[-10],'[Arista ItemsinBacklog - Template.xlsm]JSGBacklog'!C2:C16,15,0)"

Welcome to the MrExcel board!

The above formula is not a valid formula. Did you mean this?
"=VLOOKUP(RC[-10],'[Arista ItemsinBacklog - Template.xlsm]JSGBacklog'!C2:C16,15,0)"

If so, try it this way instead of using AutoFill

ActiveCell.Resize(LastRow - ActiveCell.Row + 1).SpecialCells(xlVisible).FormulaR1C1 = _
    "=VLOOKUP(RC[-10],'[Arista ItemsinBacklog - Template.xlsm]JSGBacklog'!C2:C16,15,0)"

 

[ysk]

That works!! You are awesome Peter!!

May I know why AutoFill does not work here?

 

[Peter_SSs]

That works!! You are awesome Peter!!

You're welcome. Thanks for the follow-up.

May I know why AutoFill does not work here?

Because AutoFill is designed to fill a continuous range of cells, not disjoint ranges as you get when a range is filtered.

 

[ysk]

You're welcome. Thanks for the follow-up.



Because AutoFill is designed to fill a continuous range of cells, not disjoint ranges as you get when a range is filtered.

Sorry I meant using AutoFill together with SpecialCells(xlCellTypeVisible) for filtered rows. It works for several examples I found in this forum.

 

[Peter_SSs]

Sorry I meant using AutoFill together with SpecialCells(xlCellTypeVisible) for filtered rows.

Yes, I knew you meant that. Still, the visible cells will generally not be one continuous range, but a series of disjoint ranges.

It works for several examples I found in this forum.

Can you point us to one of those so we know what you are referring to?

 

[ysk]

Yes, I knew you meant that. Still, the visible cells will generally not be one continuous range, but a series of disjoint ranges.


Can you point us to one of those so we know what you are referring to?

VBA Auto fill visible cells

Hi there, Must be something wrong with the syntax as I get a runtime error 1004 : "Autofill method of range class failed". Here is my code : Range("X6").FormulaR1C1 = "=IF(ISNA(RC[16]),"""",RC[16])" Range("X6").AutoFill Destination:=Range("X6:X" & Lastrow).SpecialCells(xlCellTypeVisible)...

www.mrexcel.com

AutoFill on Dynamic Range to Visible Cells

Hello all, I am new to Macros/VBA and have been lurking around trying to piece together something that will work for what I need to do, but I have been unsucessful. Situation: I am looking to AutoFill a dynamic column range to only visbile cells. This is what I have pieced together so far...

www.mrexcel.com

Now looking back I may have incorrectly assumed the solutions provided is part of AutoFill method

 

[Peter_SSs]

Now looking back I may have incorrectly assumed the solutions provided is part of AutoFill method

Yes, those threads did not use AutoFill despite the thread titles and in fact used the same structure that I did - particularly that first link.

 

[ysk]

Yes, those threads did not use AutoFill despite the thread titles and in fact used the same structure that I did - particularly that first link.

Understood perfectly, thank you so much for your help!

 

[Peter_SSs]

You're welcome.