Help with Looping (For and Next Statement)

[pwill]

Hi can anyone help me with understanding how to use the For and Next Statement?

I have had a go at trying to delete rows one at a time with the For and Next statement but am struggling to understand how it works?

I have put (dummy Data) in columns "A:B" and have put X's down column C in random rows. I want to delete (xlUp) rows, Range("A:C") one at a time where there is an X in column C.

I have the following macros, the first two work but I have to click the play button in the Visual Basic for the rows to delete one at a time.

The third macro is my attempt at using the For and Next statement using the first macro and want to try and do the same with the second macro if possible? It deletes the first row but then errors?

any help would be appreciated

	A	B	C	D	E
1	Header	Header	Header
2	A	1
3	B	2	x
4	C	3
5	A	4	x
6	A	5	x
7	B	6	x
8	C	7
9	B	8	x
10	A	9
11	B	10	x
12	C	11
13	A	12	x
14
15

<tbody>
</tbody>

Sub Macro1()

Dim Sht1 As Worksheet: Set Sht1 = Sheet1
Dim lRow As Long

    lRow = Sht1.Cells(, 3).End(xlDown).Row
    
    Sht1.Range("A" & lRow & ":C" & lRow).Delete (xlUp)
    
End Sub


Sub Macro2()

Dim Sht1 As Worksheet: Set Sht1 = Sheet1

    Sht1.Cells(, 3).End(xlDown).Activate
    Sht1.Range(ActiveCell.Offset(, -2), ActiveCell.Offset(0, 0)).Delete (xlUp)

End Sub


Sub Macro3()

Dim Sht1 As Worksheet: Set Sht1 = Sheet1
Dim lRow As Long
Dim i As Long
Dim Rng

    lRow = Sht1.Cells(, 3).End(xlDown).Row
    
    Set Rng = Sht1.Range("A" & lRow & ":C" & lRow)
    
    For i = 1 To lRow
        Rng.Delete (xlUp)
    Next i

End Sub

Regards

pwill

 

[My Aswer Is This]

Well it should be near the top.
Before the loop starts for sure
But if you have Dim
And then screen updating then the loop it would make no different's
Or maybe I should say it may cause a slow down of .00000002 milliseconds

 

[My Aswer Is This]

That makes a bit more sense to me now, I think I see what's going on.

I struggle with knowing what to have the 'i' equaland then where to include it on the next line. Am I right in thinking the 'i' represents the number of rows to loop through which is equal to the number of rows using 'Lastrow' and why you have it on the left in the (i, 1) and the 1 represents column A

Cells(i, 1)​

means
Cells(row,Column)
So Cells(1,1) means row 1 column 1
cells(10,5)
Means Row 10 column 5

 

[pwill]

Here is a loop that will do what you want:
It deletes the entire row.
Now you said A to K but I assumed you meant entire row

Sub My_Loop()
Application.ScreenUpdating = False
'Modified  7/21/2018  12:08:58 PM  EDT
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1
If Cells(i, "A").Value <> Cells(i, "E").Value Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub

Now if your looking for the fastest way we would maybe use another approach.

Hi My Answer Is This,

Just ran your code and it works fine, although your code is a lot shorter and does do everything in one go.
I know your not to bothered about speed but I put it to the test anyway and it took 18sec. I added Peter_SSs code to the macro I have that puts the X's in column C and it took 12 seconds, so it was quite a bit faster...

Just letting you know

thanks for all your help

pwill

 

[My Aswer Is This]

Well glad your happy with doing it your way.
It appears your running two scripts now
One script to put in all the x's and then another script to delete the rows with all the x's

But glad to know your saving 6 seconds.

 

[tonyyy]

Assuming those x's are not formula results, for me this is about 10 times faster than any of the other codes posted so far.
Code:

Sub Del_Rows()
  With Range("A2", Range("C" & Rows.Count).End(xlUp))
    .Sort Key1:=.Columns(3), Header:=xlNo
    .Columns(3).SpecialCells(xlConstants).EntireRow.Delete
  End With
End Sub

Thanks for sharing, Peter. Will file it away for future reference... (Hopefully I'll be able to find it later!)

 

[pwill]

Well glad your happy with doing it your way.
It appears your running two scripts now
One script to put in all the x's and then another script to delete the rows with all the x's

But glad to know your saving 6 seconds.

I have merged the two scripts together into one macro is what I meant, for me it's not that I'm saving 6sec, it's that the screen dosn't seem to flicker as much if it's that bit quicker.

I do prefer the code to be trimmed as much as possible.

pwill

 

[My Aswer Is This]

So show me the script you are now using? The one where you have both scripts.

 

[pwill]

Ok, give us a min

 

[My Aswer Is This]

The script I gave you in post 46 should not have screen flickering.
You can see line two of the code turns off screen updating.

 

[pwill]

Private Sub CommandButton1_Click()
    Dim Sht2 As Worksheet: Set Sht2 = Sheet02
    Dim Sht5 As Worksheet: Set Sht5 = Sheet05
    Dim FiltrB As Worksheet: Set FiltrB = Sheet06
    Dim lRowA As Long
    Dim lRowM As Long
    Dim lRow As Long
    Dim i, j, Arr
    
        Application.ScreenUpdating = False
        
        lRowM = Sht5.Cells(Rows.Count, "M").End(xlUp).Row
        FiltrB.Range("A2:K" & lRowM) = _
            Sht5.Range("M2:W" & lRowM).Value
                Sht5.Range("M2:W" & lRowM) = ""
            
            With Sht5
                Application.ScreenUpdating = False
                
                lRowA = Sht5.Cells(Rows.Count, 1).End(xlUp).Row
                Arr = Sheet06a.Cells(1, 1).CurrentRegion
    
                    For j = 1 To UBound(Arr)
                        For i = 2 To lRowA
                            If Sht5.Cells(i, 1) = Arr(j, 1) And Sht5.Cells(i, 5) <> Arr(j, 2) Then
                                Sht5.Cells(i, 12) = UCase("x")
                            End If
                        Next
                    Next
                    
                    With .Range("A2", .Range("L" & Rows.Count).End(xlUp))
                        .Sort Key1:=.Columns(12), Header:=xlNo
                        .Columns(12).SpecialCells(xlConstants).EntireRow.Delete
                    End With
            End With
        
            With FiltrB
                Application.ScreenUpdating = False
                
                lRowA = FiltrB.Cells(Rows.Count, 1).End(xlUp).Row
                Arr = Sheet06a.Cells(1, 1).CurrentRegion
    
                    For j = 1 To UBound(Arr)
                        For i = 2 To lRowA
                            If FiltrB.Cells(i, 1) = Arr(j, 1) And FiltrB.Cells(i, 5) <> Arr(j, 2) Then
                                FiltrB.Cells(i, 12) = UCase("x")
                            End If
                        Next
                    Next
                    
                    With .Range("A2", .Range("L" & Rows.Count).End(xlUp))
                        .Sort Key1:=.Columns(12), Header:=xlNo
                        .Columns(12).SpecialCells(xlConstants).EntireRow.Delete
                    End With
            End With
        
        lRow = FiltrB.Cells(Rows.Count, "A").End(xlUp).Row
        Sht5.Range("M2:W" & lRow) = _
            FiltrB.Range("A2:K" & lRow).Value
                FiltrB.Range("A2:K" & lRow) = ""
        
    Application.ScreenUpdating = True
    
End Sub