Insert Value into cells that are in a table column after a filter

Question

Not sure why my code isn't working. I am filtering a column in a table by blank values "(blanks)". Then inserting the value "NA" into the first cell and copying that value by dragging down the fill handle down to last cell in the column. Then unfiltering table again so everything is showing.

But when I run the macro all it does is copy the column header name and pastes it in the first cell and nothing else.

Sub InsertNAtoBlanks()
'
' InsertNAtoBlanks Macro
'
'
     ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=11, Criteria1:= _
        "="
     Range("K4").Select
     ActiveCell.FormulaR1C1 = "NA"
     Range("K4").Select
     Selection.FillDown
     ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=11
     End Sub

score 3 · Accepted Answer · answered Dec 13 '13 at 21:08

Looks like you used the macro recorder to getthis code. While macro recorder is a good way to see what objects to use, it can produce some pretty awfull code!

Try this strategy instead

Sub Demo()
    Dim lo As ListObject
    Dim rng As Range

    Set lo = ActiveSheet.ListObjects("Table6")
    Set rng = lo.DataBodyRange.Columns(11)
    rng.SpecialCells(xlBlanks) = "NA"
End Sub

Or reference the columns by name

Set rng = lo.ListColumns("YourColumnName").DataBodyRange

Insert Value into cells that are in a table column after a filter

1 Answers1