Looping Through Sheet
 

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

Looping Through Sheet
 

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

--
Regards,
Tom Ogilvy

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

Looping Through Sheet
 

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

scott wrote:
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

Looping Through Sheet
 

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

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