FillDown variable rows in VBA

Question

I have the below code in my MACRO for Excel 2010.

It should filter column J to find the cells not equal to 0.  The at the first cell at 14, and fill down to the end of column J.  What happens is that regardless of the number of rows, it always adds 1 extra 14 below the last row of data in Column J.  Can anyone tell me what's wrong?

'For the people who have IC = 15 and an amount in the TTW("I") they have a rate of 14%. Add 14 to TTW(J)

    Dim rngIC As Range

    Dim rngIC2 As Range

    Set rngIC = ActiveSheet.Range("J1").CurrentRegion

    rngIC.AutoFilter Field:=10, Criteria1:="<>0", Operator:=xlAnd

    Set rngIC2 = rngIC.Offset(2, 9).Resize(rngIC.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

    rngIC2.FormulaR1C1 = "14"

    ActiveSheet.Range("J1").AutoFilter

'fills column J with "14" filling one extra row, instead of filling just the first empty cell in J or filling to the end of the column

This part does not even run

'This does not work

'    Dim lrowj2 As Long

'        With ActiveSheet

'            lrowj2 = Range("rngIC2" & Rows.Count).End(xlUp).Row

'                Range("rngIC2" & lrowj2).FillDown

'        End With

Answer 1

You are offsetting by two rows, but only resizing by one fewer.

This

Set rngIC2 = rngIC.Offset(2, 9).Resize(rngIC.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

Should be either this

Set rngIC2 = rngIC.Offset(2, 9).Resize(rngIC.Rows.Count - 2, 1).SpecialCells(xlCellTypeVisible)

or this

Set rngIC2 = rngIC.Offset(1, 9).Resize(rngIC.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

And this:

This part does not even run

'This does not work

'    Dim lrowj2 As Long

'        With ActiveSheet

'            lrowj2 = Range("rngIC2" & Rows.Count).End(xlUp).Row

'                Range("rngIC2" & lrowj2).FillDown

'        End With

Might be

    Dim lrowj2 As Long

    With ActiveSheet

        lrowj2 = .Cells(.Rows.Count, rngIC2.Column).End(xlUp).Row

        .Cells(2, rngIC2.Column).Resize(lrowj2 - 1, 1).SpecialCells(xlCellTypeVisible).Value = 14

    End With

Answer 2

You are offset by 9 columns from A (that is  the first column of J's currentregion, I am guessing) and resizing it to be one column wide:

Set rngIC2 = rngIC.Offset(2, 9).Resize(rngIC.Rows.Count - 2, 1).

Answer 3

Bernie,

Thank you so much, changing that row to 2 from 1 did the trick.

One question.  Why does the below code statements fill column J?

    Dim rngIC As Range

    Dim rngIC2 As Range

    Set rngIC = ActiveSheet.Range("J1").CurrentRegion

    rngIC.AutoFilter Field:=10, Criteria1:="<>0", Operator:=xlAnd

    Set rngIC2 = rngIC.Offset(2, 9).Resize(rngIC.Rows.Count - 2, 1).SpecialCells(xlCellTypeVisible)

    rngIC2.FormulaR1C1 = "14"