Search for a word and delete the entire row.
VBA Telemetry pings you when your VBA projects fail
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,136
    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,136
    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

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
  •  

 

 
DMCA.com