Search for a word and delete the entire row.

[mdennis624]

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 ]

[Tom Urtis]

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

[Tom Urtis]

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