what I need is a sub that will read all the cells in a specified colum
and delete rows if the word "Old" in the cell anywhere.
such as big old car?

Is that possible. I have a nice sub that deletes rows according t
contents, but I don't know how to make it look between word

--
Message posted from http://www.ExcelForum.com

Hi
try the following for column A

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If InStr (Cells(RowNdx, "A").Value,Old") Then
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

what I need is a sub that will read all the cells in a specified
column and delete rows if the word "Old" in the cell anywhere.
such as big old car?

Is that possible. I have a nice sub that deletes rows according to
contents, but I don't know how to make it look between words


---
Message posted from http://www.ExcelForum.com/

One way:-

Just change the 1 in the 'With Cells(r, 1)' bit to whatever column number you
will be using.


Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

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

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
Set c = .Find(ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next r
Application.ScreenUpdating = True

End Sub

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ksnapp " wrote in message
...
what I need is a sub that will read all the cells in a specified column
and delete rows if the word "Old" in the cell anywhere.
such as big old car?

Is that possible. I have a nice sub that deletes rows according to
contents, but I don't know how to make it look between words


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004

Sub test()
Const cColumn = 2, cSearch = "f"
Dim i As Long, lngLastRow As Long

With ActiveSheet
lngLastRow = .Cells(Rows.Count, cColumn).End(xlUp).Row

For i = lngLastRow To 1 Step -1
If InStr(1, .Cells(i, cColumn).Value, cSearch) 0 Then
.Rows(i).Delete xlShiftUp
End If
Next
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ksnapp " wrote in message
...
what I need is a sub that will read all the cells in a specified column
and delete rows if the word "Old" in the cell anywhere.
such as big old car?

Is that possible. I have a nice sub that deletes rows according to
contents, but I don't know how to make it look between words


---
Message posted from http://www.ExcelForum.com/

Your fingers are too fast Frank - Missed that first pair of quotes round the
'Old':-

If InStr (Cells(RowNdx, "A").Value,Old") Then :-)

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" wrote in message
...
Hi
try the following for column A

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If InStr (Cells(RowNdx, "A").Value,Old") Then
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

what I need is a sub that will read all the cells in a specified
column and delete rows if the word "Old" in the cell anywhere.
such as big old car?

Is that possible. I have a nice sub that deletes rows according to
contents, but I don't know how to make it look between words


---
Message posted from http://www.ExcelForum.com/


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 25/02/2004