Looping through Sheet

[scott]

I'm trying to start at B1, then go down until I find a cell containing
"myword" and delete the row with "myword". I'm failing bad and need help.
I've looked at many examples like below, but can't get them to run.

Sub DeleteRows()
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 = "myword" Then
theRange.Cells(I).EntireRow.Delete
End If
Next
End Sub

 

[Don Guillett]

I just tested your code and it worked fine IF I selected the cells in col B
first. I would have written to automatically use the range
sub dr()
lc=cells(rows.count,"b").end(xlup).row
for i= lc to 1 step -1
if cells(i,2)="myword" then rows(i).delete
next i
end sub

Sub DeleteRows()
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 = "myword" Then
theRange.Cells(I).EntireRow.Delete
End If
Next
End Sub

 

[Norman Jones]

Hi Scott,

With the appropriate worksheet and the requisite cells pre-selected, your
code worked for me.

Avoiding selections, try:

Sub DeleteRows()
Dim theRange As Range, nCells As Integer, I As Integer
Dim lastRow As Long
With Sheets("Sheet1") '<<===== CHANGE TO SUIT
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set theRange = .Range("B2:B" & lastRow)
End With
nCells = theRange.Cells.Count
For I = nCells To 1 Step -1
If theRange.Cells(I).Value = "myword" Then
theRange.Cells(I).EntireRow.Delete
End If
Next
End Sub

 

[Bob Phillips]

What problem do you get, it works fine for me?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

Hi Scott,

Change:

Set theRange = .Range("B2:B" & lastRow)

to

Set theRange = .Range("B1:B" & lastRow)

 

[Guest]

What problems are you having? Your code seems to work, but only if the
entire range is selected. For example, if the selection is only the cell B1,
it will only look in that one cell, but as long as you select the entire
column (or at least the portion containing "myword") it should work. But if
you want a more user-friendly version where that won't matter you could
modify your code just a bit. Note particularly the need to change nCells and
I from Integer to Long to avoid an overflow error:

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