CODEWITHSUNDEEP
vbafilterexcel
PerfectGamesOnline.com
PerfectGamesOnline.com

Reputation: 1778

VBA Auto fill visible cells (filtered range) with xlFillSeries

I want to fill in 2..N integer series into filtered/visible range.

This code fills in the series but ignores the filtered range:

COL_SID_CURRENT = 3
COL_SID_CURRENT_STR = "c"
ROW_LAST = ActiveSheet.UsedRange.Rows.Count

' Start the series with value 2 in cell "c2"
Cells(2, COL_SID_CURRENT).FormulaR1C1 = "2"

aRangeStr = "" & COL_SID_CURRENT_STR & "2"      ' -> "c2"
Range(aRangeStr).Select

aRangeStr = aRangeStr & ":" & COL_SID_CURRENT_STR & ROW_LAST      ' -> "c2:c24"
Selection.AutoFill Destination:=Range(aRangeStr),Type:=xlFillSeries

I've tried to add the xlCellTypeVisible into the code but none of my combinations worked:

Range(aRangeStr).SpecialCells(xlCellTypeVisible).Select
Selection.AutoFill Destination:=Range(aRangeStr).SpecialCells(xlCellTypeVisible), _
   Type:=xlFillSeries

Any hint how to fill the series only on filtered/visible cells?

Upvotes: 2

Views: 8606

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Autofill doesn't work on the filtered range but there is alternative. :)

TRIED AND TESTED

You can use this formula instead (I am assuming you are filtering on the basis of what is stored in Col A (See Sample Image)

I am adding + 1 as you are starting the series with value 2 in cell "c2"

=IF(A2="FILTER_TEXT",COUNTIF($A$2:A2,"FILTER_TEXT")+1,"")

enter image description here

And to adapt it to your example, use this code.

Sub Sample()
    COL_SID_CURRENT = 3
    COL_SID_CURRENT_STR = "c"

    ROW_LAST = ActiveSheet.UsedRange.Rows.Count

    ' Start the series with value 2 in cell "c2"
    Cells(2, COL_SID_CURRENT).Formula = "=IF(A2=""FILTER_TEXT"",COUNTIF($A$2:A2,""FILTER_TEXT"")+1,"""")"

    aRangeStr = "" & COL_SID_CURRENT_STR & "2"      ' -> "c2"

    aRangeStr = aRangeStr & ":" & COL_SID_CURRENT_STR & ROW_LAST      ' -> "c2:c24"
    Range(aRangeStr).Formula = "=IF(A2=""FILTER_TEXT"",COUNTIF($A$2:A2,""FILTER_TEXT"")+1,"""")"
End Sub

Please change "FILTER_TEXT" to the relevant text. Also you might need to change the Reference Range. Like I mentioned, I am assuming that the filter is based on Values in Col A.

Upvotes: 2

Related Questions