Assistance with code Please

G

Greg B...

I have copied the code below to see if someone can help me with my problem.
I work great unfortunately it needs to check from cells d2 to d101. I have
to hit run about 5 times for the whole range to be cleared. I sthere anyway
to loop it until it is done or have it complete in one shot.

Can someone tell me what I am doing wrong

Sub CopyData()
Dim rng As Range, cell As Range, col As Long
Dim rw As Long, rng2 As Range
col = 4
rw = 1

With Worksheets("SCHEDULE")
Set rng = .Range(.Cells(col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete

End If
Next

End Sub
Thanks in advance

Greg
 
T

Trevor Shuttleworth

Greg

try it this way:

Sub CopyData()
Dim rng As Range, cell As Range, RngToDelete As Range, col As Long
Dim rw As Long, rng2 As Range
col = 4
rw = 1

With Worksheets("SCHEDULE")
Set rng = .Range(.Cells(col), .Cells(Rows.Count, col).End(xlUp))
End With
For Each cell In rng
If LCase(cell.Value) = "yes" Then
If RngToDelete Is Nothing Then
Set RngToDelete = cell
Else
Set RngToDelete = Union(RngToDelete, cell)
End If
End If
Next
If Not RngToDelete Is Nothing Then
RngToDelete.EntireRow.Delete
End If
End Sub

Regards

Trevor
 
D

Dave Peterson

This link looks funny to me:

Set rng = .Range(.Cells(col), .Cells(Rows.Count, col).End(xlUp))

Did you mean:

Set rng = .Range(.Cells(rw, col), .Cells(Rows.Count, col).End(xlUp))

(added "rw" in the first .cells() portion.)
 
M

Myrna Larson

Just so you understand why your original code failed, when using For Each, the
rows are processed in ascending order. Let's say you delete row 2. The code
then goes on to row 3. Problem is, the original row 3 has moved up to row 2
because of the deletion, so you really want to check row 2 again.

The solution for this is to process the rows from the bottom up. That means
you can't use For Each loop, but a "standard" For/Next loop with the row
number:

RLast = Cells(65536,1).End(xlUp).Row
For R = RLast To 1 Step - 1
If Cells(R, 1).Value = "X" Then Rows(R).EntireRow.Delete
Next R
 
T

Trevor Shuttleworth

Dave

I didn't notice that and you are right. However, in my testing it works
fine ... and I'm not sure why.

?rng.Address
$D$1:$D$17

Any thoughts ?

Regards

Trevor
 
D

Dave Peterson

Try it with a workbook with two worksheets.

And make sure sheets(1) isn't the activesheet.

Those unqualified range objects usually refer to the activesheet. If the
activesheet is not the correct sheet, then you get an error.
 
G

Greg B

Ok thanks never thought of it that way

Will keep it in mind from now on

Thanks for the advice

Greg
 
Top