Find in a column

[teachman]

Hello,

I want to find the string "Size" in a specific column and then delete that row. I'm having problems with the find function. When the sub is stepped thru I get a Type Mismatch error.

Here is the Find code.

    ActiveSheet.Columns("C:C").Find( _
        What:="Size", _
        After:=ActiveCell, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=True, _
        SearchFormat:=False).Activate
    Rows(ActiveCell.Row).Delete

I want to know where in this there is a type mismatch and how to fix it.

Thanks,
George

 

[rpaulson]

George,

Try this

With Range("C:C")
        .Replace "Size", True, xlWhole ' can also use *Size* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With

hth,

Ross

 

[Fluff]

You'll get the type mismatch error if the active cell is not in column C

 

[teachman]

Ross,

That worked like a charm. There were over 28,000 rows to look in and it all happened in the blink of an eye!

Thanks for the help,

George Teachman