Delete all rows with designated word

  • Thread starter Thread starter PHILLY
  • Start date Start date
P

PHILLY

I'm looking for code that looks in one specific column for
a certain word ("delete") and deletes all those rows in
which it appears. I'm trying to use "Find" so that I can
delete them all at once rather than have it cycle through
each row. I'm sure there is a better way.........


Thanks!
 
Try this

It will look for "Find" in the A column in Worksheets("Sheet1")

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "Find" Then .Rows(r).Delete
Next
End With
Application.ScreenUpdating = True
End Sub
 
Pure genius! Now, can you explain it to the humans?


-----Original Message-----
Try this

It will look for "Find" in the A column in Worksheets ("Sheet1")

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete
Next
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"PHILLY" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
One more question: How can I substitute the active
worksheet name for "Sheet 1" (the worksheet name will be
different each time)?

TIA
-----Original Message-----
Try this

It will look for "Find" in the A column in Worksheets ("Sheet1")

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "Find" Then .Rows (r).Delete
Next
End With
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"PHILLY" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi
For r = .UsedRange.Rows.Count To 1 Step -1
Click to expand...
This will find the last row in the UsedRange and will loop from the bottem up to row 1

r = the row number in the loop
We use Cells(row,column) to see if the cell in column A have the text "Find" in it and delete the row
if the text is "Find"
If .Cells(r, "A").Value = "Find" Then .Rows(r).Delete
Click to expand...
 
Just another couple of ways (I think the 2nd was Chip Pearson's):-

Sub DelRows()

ans = InputBox("What string do you want rows to be deleted if they contain it?")
Application.ScreenUpdating = False

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(lastrow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:=ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

-----------------------------------------------------------

Sub DelRowsIf()

Dim RowNdx As Long
Dim LastRow As Long
Dim ans As String

ans = InputBox("What string do you want rows to be deleted if it contains it?")

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "A"), ans, vbBinaryCompare) = 0 Then
Cells(RowNdx, "A").EntireRow.Delete
End If
Next RowNdx

End Sub
 
Can Ron de Bruin's code be modified to hide a row instead of deleting it?
If so, how?

TIA
Muzzy
 
Back
Top