PDA

View Full Version : Solved: delete rows



oleg_v
03-16-2010, 06:05 AM
hi

i need macro that will delete every row that contains a word "part"

thanks

mbarron
03-16-2010, 06:24 AM
Is there a specific column to be searched? Is it the entire cell or a word within multiple words in the cell?

oleg_v
03-16-2010, 06:33 AM
the word takes entire cell
the search is in sheet1
there is no specific column because the columns always changes but no more than 1 column

mbarron
03-16-2010, 06:33 AM
This macro will delete all rows where the word "part" is part of the cells in column F

Sub NoPart()

Range("A1").AutoFilter Field:=6, Criteria1:="=*part*", _
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Range(Cells(2, "f"), Cells(2, "f").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("a1").AutoFilter
Range("a1").Select

End Sub

mbarron
03-16-2010, 06:45 AM
This version will ask you to choose the column you want to inspect. It assumes the table starts in Cell A1.
Sub NoPart()

Dim rRow As Range

Set rRow = Application.InputBox("Choose Column", Default:=Selection.Address, Type:=8)

Range(rRow.Address).AutoFilter Field:=rRow.Column, Criteria1:="=part", _
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
On Error Resume Next
Range(Cells(2, rRow.Column), Cells(2, rRow.Column).End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
Range(rRow.Address).AutoFilter
rRow.Select
End Sub

oleg_v
03-16-2010, 06:51 AM
thanks