Loop Help --> QUICK!!!! 1

[jessedh]

Hi, This seems to be the correct syntax but it doesn't work, this is the first loop i have ever wrote so any help is appreciated. When I run it I get a "method of range" failed message...

Dim A As String
Dim Q As String
Dim X As Variant
Dim Y As Variant
Dim Z As Variant

A = "A"
Q = "Q"
X = "O"
Y = 1

A = A & Y
Q = Q & Y
Z = X & Y


Do While Y <= 606
If Range(Z) = 0 Then
Range(A & &quot;:&quot; & Q).Select
Range(Q).Activate
Selection.Delete Shift:=xlUp
End If
Range(Z).Select
Y = Y + 1
Loop

 

[segmentationfault]

Could you be more specific about where the problem occurs? I ran this on a blank sheet and it seems to delete 605 rows without a problem. I do have a question about the line

Z = X & Y

and then using Y as an iterator. A better way to initialize Z would be

Z = X & "1"

 

[swisslog]

Y is affected on execution time!

Try this:

Do While Y <= 606
If Range(&quot;1:&quot; & Y) = 0 Then
Range(A & &quot;:Q&quot; & Y).Select
Range(&quot;Q&quot; & Y).Activate
Selection.Delete Shift:=xlUp
End If
Range((&quot;1:&quot; & Y).Select
Y = Y + 1
Loop

better:

FOR Y= 1 TO 606 DO
If Range(&quot;1:&quot; & Y) = 0 Then
Range(A & &quot;:Q&quot; & Y).Select
Range(&quot;Q&quot; & Y).Activate
Selection.Delete Shift:=xlUp
End If
Range((&quot;1:&quot; & Y).Select
NEXT Y

 

[jessedh]

what does Y is effected at excution time mean?

Do While Y <= 606
If Range(&quot;1:&quot; & Y) = 0 Then
'How would This work if I wanted to test Q1,Q2 Etc???

Range(A & &quot;:Q&quot; & Y).Select
Range(&quot;Q&quot; & Y).Activate
Selection.Delete Shift:=xlUp
End If
Range((&quot;1:&quot; & Y).Select
Y = Y + 1
Loop

THANKS!!!!!!

 

[swisslog]

Y is affected on execution time means that the value of Y changes when your program executes.

The statement &quot;A = A & Y&quot; will put the value &quot;AY&quot; into the variable &quot;A&quot;, no matter if the content of Y changes after the statement...

sorry for the syntax faults, I was a bit in a hurry:
FOR Y= 1 TO 606 DO
    If Range(&quot;1:&quot; & Y) = 0 Then
        Range(&quot;A1 :Q&quot; & Y).Select
        Range(&quot;Q&quot; & Y).Activate
        Selection.Delete Shift:=xlUp
    End If
    Range((&quot;1:&quot; & Y).Select
NEXT Y
If your intention is to eliminate all lines where &quot;0&quot; is indicated in column &quot;O&quot; then:

FOR Y= 606 TO 1 step -1 DO
    If Range(&quot;O:&quot; & Y) = 0 Then
        Range(Y & &quot;:&quot; & Y).Select
        Selection.Delete Shift:=xlUp
    End If
NEXT Y

will do the affair... (not tested!)