Deleting entire rows that contain a specific word

[Tom]

How do I delete an entire row in excel that contains
the word "construction"

 

[Frank Kabel]

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If instr(Cells(row_index, 1).Value,"Construction") then
rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub

also have a look at:
http://www.xldynamic.com/source/xld.Deleting.html

 

[Ron de Bruin]

Hi Tom

If there a error in one of the cells of the column Frank's code will not work.(Error)

Try this

Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1

If IsError(Cells(row_index, 1).Value) Then
' do nothing
ElseIf InStr(Cells(row_index, 1).Value, "Construction") Then
Rows(row_index).Delete
End If

Next
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

A manual method if your word is in a single column:

Apply Data|filter|autofilter to that column.
filter to show just "construction"
(or custom contains "construction" if there's more text in the cell.)
delete those visible rows
Remove the filter.

You could also do the equivalent of Edit|Find in a macro that keeps looking
until it can't find anymore.

I searched only column A and only looked at cells that only contained
"construction" (xlwhole). But you could change it if you wanted.

Option Explicit
Sub testme()
Dim myWord As String
Dim FoundCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

myWord = "construction"

With wks.Range("a:a")
Do
Set FoundCell = .Cells.Find(what:=myWord, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm