Assistance with code Please

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

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


"Greg B..." <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>

Thank you Trevor


Greg

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.)

"Greg B..." wrote:
>
> Thank you Trevor
>
> Greg

--

Dave Peterson

Thanks for all your help

Greg

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


On Thu, 3 Mar 2005 01:17:03 +1030, "Greg B" <[email protected]> wrote:

>Thanks for all your help
>
>Greg
>

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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.)
>
> "Greg B..." wrote:
>>
>> Thank you Trevor
>>
>> Greg
>
> --
>
> 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.



Trevor Shuttleworth wrote:
>
> 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
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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.)
> >
> > "Greg B..." wrote:
> >>
> >> Thank you Trevor
> >>
> >> Greg
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Ok thanks never thought of it that way

Will keep it in mind from now on

Thanks for the advice

Greg