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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
pwill said:
... so when the rows are deleted from A:K the rows in M:W get deleted too..
Click to expand...
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.

Rich (BB 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
 
Last edited:
Upvote 0
Peter_SSs said:
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?
Click to expand...

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

Code: 
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
 
Upvote 0
Peter_SSs said:
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.

Rich (BB 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
Click to expand...

Taking a look at that now... :)
 
Last edited:
Upvote 0
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.
 
Upvote 0
Peter_SSs said:
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.

Rich (BB 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
Click to expand...

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

Noted, Thanks for that :)
 
Upvote 0
pwill said:
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
Click to expand...

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
 
Last edited:
Upvote 0
pwill said:
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 :)
Click to expand...
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. :)
 
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,746
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