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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.