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

 

[pwill]

Sorry about that had to edit it

 

[pwill]

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.

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

 

[My Aswer Is This]

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.

 

[pwill]

Yes for now at least, but would welcome your help in the future with other things if you have the time.

pwill

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Yes for now at least, but would welcome your help in the future with other things if you have the time.

pwill

 

[Peter_SSs]

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

... faster would be great faster the better as far as I'm concerned..

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.

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

 

[pwill]

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.

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

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

 

[pwill]

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.

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

 

[pwill]

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.

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

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

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

 

[Peter_SSs]

It took 12sec with how I had it in post #60 and less than a sec with this code ..

Waiting less than 1 second compared to waiting 12 to 18 seconds is definitely a worthwhile saving!

.. and no screen flickering

.. and note that my code did not turn screen updating off.

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

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?