Help with Looping (For and Next Statement)

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
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

[TABLE="width: 207"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Code: 
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
 
Last edited:
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
pwill said:
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
Click to expand...


Cells(i, 1)​

means
Cells(row,Column)
So Cells(1,1) means row 1 column 1
cells(10,5)
Means Row 10 column 5
 
Upvote 0
My Aswer Is This said:
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
Code: 
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.
Click to expand...

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
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
Click to expand...

Thanks for sharing, Peter. Will file it away for future reference... (Hopefully I'll be able to find it later!)
 
Upvote 0
My Aswer Is This said:
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.
Click to expand...

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Combine input box (multiple criteria)
Replies
4
Views
115
gomes123
G
A
  • Question Question
Clock in and out 2 breaks and a lunch
Replies
0
Views
124
Aiden Jenner
A
T
  • Question Question
Number of Pages Doesn't Update in Loop
Replies
1
Views
230
bobsan42
bobsan42
Guinaba
  • Question Question
Tunning a VBA macro
Replies
1
Views
94
Skyybot
Skyybot
E
  • Solved
Delete Row if
Replies
6
Views
160
Eric Penfold
E

Forum statistics

Threads
1,223,980
Messages
6,175,764
Members
452,668
Latest member
mrider123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top