reading inside a text string

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

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
 
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
 
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
 
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
 
Back
Top