Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Search for a word and delete the entire row.

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    York, PA
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For Excel 97: I need a macro to search a column (B) for cells that might contain a specific word ("space")within the contents of the cells. If the cell contains the word ("space"), delete the entire row. ie. If the cell contents = "This is a space", the macro would delete the entire row for any occurence of the word "space" in the column search.

    [ This Message was edited by: mdennis624 on 2002-02-18 19:23 ]

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code assumes column A; modify as needed.

    Sub DeleteSpaceRows()
    Dim iRow As Long
    For iRow = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Cells(iRow, 1) = "space" Then Rows(iRow).Delete
    Next iRow
    End Sub

    Tom Urtis

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dennis,

    I re-read your post and noticed after my first reply that the word "space" might be in a string of words in a single cell.

    Try this macro instead then, it avoids loops by replacing "space" with nothing, hence a blank cell, and then deletes the rows with a blank cell in column A. Modify for column range.

    Sub DeleteSpace()
    With Columns("A:A")
    .Replace What:="*space*", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    End Sub

    HTH

    Tom Urtis

Some videos you may like

User Tag List

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
  •