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]

The thing is, it's actually a different set of data in columns M:W and has more rows of data so when the rows are deleted from A:K the rows in M:W get deleted too..

 

[pwill]

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

Noted...

 

[Peter_SSs]

... so when the rows are deleted from A:K the rows in M:W get deleted too..

Not if we are more judicious about how we do the deletions from A:K.
Assuming that there is at least one spare column between the data sets, as there seems to be for your data (column L) then we should still be able to do it all on Sht5 with only a slightly expanded code.

Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant, LC As Variant
  Dim r As Long, k As Long, rws As Long
  
  Const LeftColumns As String = "A M" '<- Left hand column of each data section
  Const ColsInSection As Long = 11    '<- Number of columns in each data section
  Const fr As Long = 2                '<- First actual data row

  Set Sht5 = Sheet05
  For Each LC In Split(LeftColumns)
    rws = Sht5.Cells(Sht5.Rows.Count, LC).End(xlUp).Row - fr + 1
    k = 0
    With Sht5.Range(LC & fr).Resize(rws, ColsInSection + 1)
      Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & rws & ")"), 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(ColsInSection + 1).Value = x
        .Sort Key1:=.Columns(ColsInSection + 1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
        .Resize(k).Delete Shift:=xlUp
      End If
    End With
  Next LC
End Sub

 

[pwill]

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?

It Doesn't have to happen on sheet06 if there is another way?

I had a go my self and this works, what do you think of having it this way...

Private Sub CommandButton111_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 s As Long, l As Long, nd As Long
    Dim Data As Variant, x As Variant
    Dim DataB As Variant, y 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)
        
            nd = .Columns.Count
            DataB = Application.Index(.Resize(, 5), Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
            
            ReDim y(1 To UBound(DataB), 1 To 1)
            For s = 1 To UBound(DataB)
                If DataB(s, 1) <> DataB(s, 2) Then
                    l = l + 1
                    y(s, 1) = "x"
                End If
            Next s
                If l > 0 Then
                    .Columns(nd).Value = y
                    .Sort Key1:=.Columns(nd), order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
                    .Resize(l).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

 

[pwill]

Not if we are more judicious about how we do the deletions from A:K.
Assuming that there is at least one spare column between the data sets, as there seems to be for your data (column L) then we should still be able to do it all on Sht5 with only a slightly expanded code.

Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant, LC As Variant
  Dim r As Long, k As Long, rws As Long
  
  Const LeftColumns As String = "A M" '<- Left hand column of each data section
  Const ColsInSection As Long = 11    '<- Number of columns in each data section
  Const fr As Long = 2                '<- First actual data row

  Set Sht5 = Sheet05
  For Each LC In Split(LeftColumns)
    rws = Sht5.Cells(Sht5.Rows.Count, LC).End(xlUp).Row - fr + 1
    k = 0
    With Sht5.Range(LC & fr).Resize(rws, ColsInSection + 1)
      Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & rws & ")"), 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(ColsInSection + 1).Value = x
        .Sort Key1:=.Columns(ColsInSection + 1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
        .Resize(k).Delete Shift:=xlUp
      End If
    End With
  Next LC
End Sub

Taking a look at that now...

 

[Peter_SSs]

Should have mentioned it much earlier in the thread but it is best if you don't fully quote long(ish) posts as it makes the thread harder to read/navigate. If you want to quote, just quote small, relevant parts only.

 

[pwill]

Not if we are more judicious about how we do the deletions from A:K.
Assuming that there is at least one spare column between the data sets, as there seems to be for your data (column L) then we should still be able to do it all on Sht5 with only a slightly expanded code.

Private Sub CommandButton1_Click()
  Dim Sht5 As Worksheet
  Dim Data As Variant, x As Variant, LC As Variant
  Dim r As Long, k As Long, rws As Long
  
  Const LeftColumns As String = "A M" '<- Left hand column of each data section
  Const ColsInSection As Long = 11    '<- Number of columns in each data section
  Const fr As Long = 2                '<- First actual data row

  Set Sht5 = Sheet05
  For Each LC In Split(LeftColumns)
    rws = Sht5.Cells(Sht5.Rows.Count, LC).End(xlUp).Row - fr + 1
    k = 0
    With Sht5.Range(LC & fr).Resize(rws, ColsInSection + 1)
      Data = Application.Index(.Resize(, 5), Evaluate("row(1:" & rws & ")"), 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(ColsInSection + 1).Value = x
        .Sort Key1:=.Columns(ColsInSection + 1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
        .Resize(k).Delete Shift:=xlUp
      End If
    End With
  Next LC
End Sub

Again Wow...very impressive, runs very smooth and very fast, all on the same sheet, can't thank you enough Peter you certainly know your stuff

 

[pwill]

Should have mentioned it much earlier in the thread but it is best if you don't fully quote long(ish) posts as it makes the thread harder to read/navigate. If you want to quote, just quote small, relevant parts only.

Noted, Thanks for that

 

[pwill]

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

Now I'm going to have a go at adding the final part to the code for column D.

All it does is a count, so I will add it to the end off your code and let you know how I get on.

Thanks for your help Peter, My Answer Is This and tonyyy

very much appreciate all your help...

pwill

 

[Peter_SSs]

Again Wow...very impressive, runs very smooth and very fast, all on the same sheet, can't thank you enough Peter you certainly know your stuff

Thanks for your generous comments. Glad it worked so well for you.


I guess the exercise has something of a lesson if you have further questions in the future. Best to tell/show us as clearly as you can what you have, what you want to end up with and what you have tried so far. However, best not to tell us how the problem should be solved as there may be ways that you haven't even thought of or know about.