Results 1 to 4 of 4

Thread: Find in a column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find in a column

    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.

    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

  2. #2
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    1,045
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find in a column

    George,

    Try this
    Code:
    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

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find in a column

    You'll get the type mismatch error if the active cell is not in column C
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Aug 2011
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find in a column

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •