Find first blank cell after Auto Filter

[hutch@edge.net]

Greetings, Gurus.

Suppose I am using auto filter on a range to filter down to only the blank cells in column C, and then I want to enter a formula into all those blank cells. How would I determine the range of the first blank cell? Here's what I have so far:

Sub Macro1()
    Range("C1").AutoFilter Field:=3, Criteria1:="="
    Range("C477").Select 'Needs to be first visible cell after the autofilter is applied.
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[PLine Listing.xlsx]COM_ REPLEN1677'!C1:C2,2,FALSE)"
    Range("C477").Copy 'Same as above
    Range("C477:C" & Cells(Rows.Count, "A").End(xlUp).Row).Select
    ActiveSheet.Paste
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveSheet.AutoFilterMode = False
End Sub

Thanks in advance for any help you can offer.

[MickG]

Hi, Could you use this, The "Specialcells" function will Give you the addresses of the Blank Cells, and then next line will fill them with th Formula.

Sub MG04May18
Dim Rng As Range
Set Rng = Range(Range("C1"), Range("C" & Rows.count).End(xlUp))
Set Rng = Rng.SpecialCells(xlCellTypeBlanks)
MsgBox Rng.address
Rng.FormulaR1C1 = "=VLOOKUP(RC[-2],'[PLine Listing.xlsx]COM_ REPLEN1677'!C1:C2,2,FALSE)"

End Sub

Regards Mick

[hutch@edge.net]

Thanks for the reply, Mick. When I tried your code, I got the following error:
Run-time error '1004': No cells were found. The debug highlighted the following line:

Set Rng = Rng.SpecialCells(xlCellTypeBlanks)

Yes, there are blank cells in the range.

[MickG]

Hi, I know this sounds rather obvious but, I can only suggest you try the code on a new sheet.
Place data in column "C", then Delete some of the values.
See what the code returns.
Regards Mick

[hutch@edge.net]

Sorry so slow to repond, Mick. I had to leave the office early yesterday.

I figured out the problem. The reason your script isn't finding any empty cells is because of the line:

Set Rng = Range(Range("C1"), Range("C" & Rows.count).End(xlUp))

It is telling it to only look in cells that already contain data. How would I chnage that line to look at all cells in column C, down to the last row in column A. I tried

Set Rng = Range(Range("C1"), Range("C" & Rows.Count, "A").End(xlUp))

but I get a "Method 'Range' of object '_Global' failed" error.

[MickG]

Hi, Try :-

Set rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp)).Offset(, 2)

Regards Mick

[hutch@edge.net]

Perfect! Here's the final code as I use it:

Sub Lookup_Pline()
    Dim Rng As Range
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
    Set Rng = Rng.SpecialCells(xlCellTypeBlanks)
    Rng.FormulaR1C1 = "=VLOOKUP(RC[-2],'[PLine Listing.xlsx]COM_ REPLEN1677'!C1:C2,2,FALSE)"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C1").AutoFilter Field:=3, Criteria1:="=#N/A", _
        Operator:=xlAnd
    Range("A1").Select
End Sub

Thanks, Mick. Have a good one.