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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
My Aswer Is This said:
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.
Click to expand...

I know, I think it's because I have other things going on at the same time so the faster the code the less flickering...
 
Last edited:
Upvote 0
So now we see. You have a lot more going on.
Screen flickering should not be happening if you have screen updating turned off.
Well I guess your set for now.
 
Upvote 0
Yes for now at least, but would welcome your help in the future with other things if you have the time.

pwill
 
Upvote 0
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
pwill said:
Yes for now at least, but would welcome your help in the future with other things if you have the time.

pwill
Click to expand...
 
Upvote 0
pwill said:
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...
Click to expand...
pwill said:
... faster would be great faster the better as far as I'm concerned..
Click to expand...
I am not quite sure what you are doing with those various sheets in the code in post 60, but with the data in Sheet05 and the button on Sheet02, I'd be interested to know how this goes for speed compared to those other codes that you timed, and of course whether it does what you want. :)
Note that this is also a looping code but I'm expecting it to be considerably faster than the others over about 8000 rows.
For testing speed, make sure you do run the code by clicking the command button on sheet2.
Code: 
Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant
  Dim r As Long, k As Long, nc As Long

  Set Sht5 = Sheet05
  With Sht5.Range("A2:L" & Sht5.Range("A" & Sht5.Rows.Count).End(xlUp).Row)
    nc = .Columns.Count
    Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
    ReDim x(1 To UBound(Data), 1 To 1)
    For r = 1 To UBound(Data)
      If Data(r, 1) <> Data(r, 2) Then
        k = k + 1
        x(r, 1) = "x"
      End If
    Next r
    If k > 0 Then
      .Columns(nc).Value = x
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
      .Resize(k).EntireRow.Delete
    End If
  End With
End Sub
 
Upvote 0
Peter_SSs said:
I am not quite sure what you are doing with those various sheets in the code in post 60, but with the data in Sheet05 and the button on Sheet02, I'd be interested to know how this goes for speed compared to those other codes that you timed, and of course whether it does what you want. :)
Note that this is also a looping code but I'm expecting it to be considerably faster than the others over about 8000 rows.
For testing speed, make sure you do run the code by clicking the command button on sheet2.
Code: 
Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant
  Dim r As Long, k As Long, nc As Long

  Set Sht5 = Sheet05
  With Sht5.Range("A2:L" & Sht5.Range("A" & Sht5.Rows.Count).End(xlUp).Row)
    nc = .Columns.Count
    Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
    ReDim x(1 To UBound(Data), 1 To 1)
    For r = 1 To UBound(Data)
      If Data(r, 1) <> Data(r, 2) Then
        k = k + 1
        x(r, 1) = "x"
      End If
    Next r
    If k > 0 Then
      .Columns(nc).Value = x
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
      .Resize(k).EntireRow.Delete
    End If
  End With
End Sub
Click to expand...

Hi Pete, Thanks for that.

Just woke up, here in the UK :)

Gonna get some Coffee and breakfast :)

I will take a look and let you know how I get on :)

pwill
 
Last edited:
Upvote 0
Peter_SSs said:
I am not quite sure what you are doing with those various sheets in the code in post 60, but with the data in Sheet05 and the button on Sheet02, I'd be interested to know how this goes for speed compared to those other codes that you timed, and of course whether it does what you want.
Click to expand...

Having a look now Peter, give me 10 - 15min and I will let you know how I get on. Also I'm trying to merge a third part into the code that fills Column D in post #45 that you might be able to speed up?

pwill
 
Last edited by a moderator:
Upvote 0
Peter_SSs said:
I am not quite sure what you are doing with those various sheets in the code in post 60, but with the data in Sheet05 and the button on Sheet02, I'd be interested to know how this goes for speed compared to those other codes that you timed, and of course whether it does what you want. :)
Note that this is also a looping code but I'm expecting it to be considerably faster than the others over about 8000 rows.
For testing speed, make sure you do run the code by clicking the command button on sheet2.
Code: 
Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant
  Dim r As Long, k As Long, nc As Long

  Set Sht5 = Sheet05
  With Sht5.Range("A2:L" & Sht5.Range("A" & Sht5.Rows.Count).End(xlUp).Row)
    nc = .Columns.Count
    Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
    ReDim x(1 To UBound(Data), 1 To 1)
    For r = 1 To UBound(Data)
      If Data(r, 1) <> Data(r, 2) Then
        k = k + 1
        x(r, 1) = "x"
      End If
    Next r
    If k > 0 Then
      .Columns(nc).Value = x
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
      .Resize(k).EntireRow.Delete
    End If
  End With
End Sub
Click to expand...

Hi Peter,

Wow... That is fast, very impressive :)

It took 12sec with how I had it in post #60 and less than a sec with this code and no screen flickering :)

Sorry it took so long, I was trying to figure out how to include FilterB could you have a look? If you duplicate columns A:K to M:W I'm trying to get M:W to do the same on sht06 then put back to sht05

here's what I have tried

Code: 
Private Sub CommandButton1_Click()
    Dim Sht5 As Worksheet: Set Sht5 = Sheet05
    Dim FiltrB As Worksheet: Set FiltrB = Sheet06
    Dim r As Long, k As Long, nc As Long
    Dim Data As Variant, x As Variant
    Dim lRowA As Long
    Dim lRowM As Long
    Dim lRow As Long
    
        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.Range("A2:L" & Sht5.Range("A" & Sht5.Rows.Count).End(xlUp).Row)
        
            nc = .Columns.Count
            Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
            
            ReDim x(1 To UBound(Data), 1 To 1)
            For r = 1 To UBound(Data)
                If Data(r, 1) <> Data(r, 2) Then
                    k = k + 1
                    x(r, 1) = "x"
                End If
            Next r
                If k > 0 Then
                    .Columns(nc).Value = x
                    .Sort Key1:=.Columns(nc), order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
                    .Resize(k).EntireRow.Delete
                End If
        End With
  
        With FiltrB.Range("A2:L" & FiltrB.Range("A" & FiltrB.Rows.Count).End(xlUp).Row)
        
            nc = .Columns.Count
            Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
            
            ReDim x(1 To UBound(Data), 1 To 1)
            For r = 1 To UBound(Data)
                If Data(r, 1) <> Data(r, 2) Then
                    k = k + 1
                    x(r, 1) = "x"
                End If
            Next r
                If k > 0 Then
                    .Columns(nc).Value = x
                    .Sort Key1:=.Columns(nc), order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
                    .Resize(k).EntireRow.Delete
                End If
        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

Thanks
 
Last edited:
Upvote 0
pwill said:
It took 12sec with how I had it in post #60 and less than a sec with this code ..
Click to expand...
Waiting less than 1 second compared to waiting 12 to 18 seconds is definitely a worthwhile saving! :)


pwill said:
.. and no screen flickering :)
Click to expand...
.. and note that my code did not turn screen updating off.


pwill said:
If you duplicate columns A:K to M:W I'm trying to get M:W to do the same on sht06 then put back to sht05
Click to expand...
1. You don't really mean duplicate do you? Just another set of similar style of data in those columns?
2. Does it really have to happen on sht06 and then be brought back to sht05? If we can do it all on the original sht05 would that be acceptable?
 
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
92
Skyybot
Skyybot
E
  • Solved
Delete Row if
Replies
6
Views
159
Eric Penfold
E

Forum statistics

Threads
1,223,975
Messages
6,175,745
Members
452,667
Latest member
vanessavalentino83

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