Delete all rows with designated word
 

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!

Delete all rows with designated word
 

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)
www.rondebruin.nl



"PHILLY" wrote in message ...
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!

Delete all rows with designated word
 

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)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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!


.

Delete all rows with designated word
 

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)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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!


.

Delete all rows with designated word
 

Hi

For r = .UsedRange.Rows.Count To 1 Step -1
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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"PHILLY" wrote in message ...
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)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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!


.

Delete all rows with designated word
 

Use

With ActiveSheet
Instead of
With Worksheets("Sheet1")



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"PHILLY" wrote in message ...
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)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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!


.

Delete all rows with designated word
 

That's the ticket, thanks again.


-----Original Message-----
Use

With ActiveSheet
Instead of
With Worksheets("Sheet1")



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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)
www.rondebruin.nl



"PHILLY" wrote in
message ...
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!


.



.

Delete all rows with designated word
 

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


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



"PHILLY" wrote in message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003

Delete all rows with designated word
 

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

TIA
Muzzy



"PHILLY" wrote in message
...
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!

Delete all rows with designated word
 

Use this line Muzzy

If .Cells(r, "A").Value = "Find" Then .Rows(r).Hidden = True


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Muzzy" wrote in message ...
Can Ron de Bruin's code be modified to hide a row instead of deleting it?
If so, how?

TIA
Muzzy



"PHILLY" wrote in message
...
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!