Looping Through Sheet

[scott]

My Below Sub accepts a string, finds it and deletes the row in which the
string is found. Can someone help me modify it to continue searching through
all cells that contain data and delete any other rows where the string
occurs?

USAGE:

DeleteRowswString("myword")

CODE

Sub DeleteRowwString(ByVal sString As String)

Dim theRange As Range, nCells As Integer, I As Integer
Set theRange = Selection
nCells = theRange.Cells.Count
For I = nCells To 1 Step -1
If theRange.Cells(I).Value = sString Then
theRange.Cells(I).EntireRow.Delete
End If
Next
End Sub

 

[Tom Ogilvy]

Sub DeleteRowwString(ByVal sString As String)

Dim theRange As Range, nCells As Integer, I As Integer
Set theRange = Intersect(Activesheet.UsedRange.Entirerow, _
Activesheet.Columns(1)).Cells
nCells = theRange.Rows(theRange.rows.count).Row
For I = nCells To 1 Step -1
If Application.countif(Cells(i,1).EntireRow,"*" & sString _
& "*") > 0 then
Cells(i,1).EntireRow.Delete
End If
Next
End Sub

 

[Dick Kusleika]

Scott

You might find using the Find method to be faster than looping through all
the cells. Try this

Sub DeleteRowwString(ByVal sString As String)

Dim rFound As Range
Dim rLook As Range

If TypeName(Selection) = "Range" Then
Set rLook = Selection

Set rFound = rLook.Find(sString, , xlValues, xlWhole)

If Not rFound Is Nothing Then
Do
rFound.EntireRow.Delete
Set rFound = rLook.FindNext
Loop Until rFound Is Nothing
End If
End If

End Sub

 

[scott]

thanks guys, i'm a database programmer that's trying to learn spreedsheet
syntax.