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
My Aswer Is This said:
Pwill

Are you saying column C only has blank cells or cells with just a x

Your image seems to indicate that but you did not say that in words.
Does column C have any thing other then x
Click to expand...

Hi My Answer Is This,

Just reading through the posts, I was just having a go at using the 'For and Next Statements' in my original post for learning purposes, sorry for any confusion.

Column C only has "X's" the rest are blank cells, I should have said I have a macro that generates the "X's" for me in column C to mark what rows to delete, I will try to be clearer with my questions in future.

I was playing around with the idea of selecting the row at "A:C" where the first X appears in column C then deleting that selection xlUp and repeating the process down to the next "X" in Column C. Little did I know how slow that would be, it just so happens that the filter method is much better for my purposes and so appreciate all the feed back.

I still want to experiment with the 'For and Next Statements' just for learning purposes and so will take a look at the link tonyyy provided and see if I can progress with that.


Regards

pwill
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Pwill

Glad to see your learning Vba

So you have a script which puts x's in column C so you know what rows to delete
And then you planned to have another script go down the sheet and delete all rows with a x in column C

We could just write one script to do this

So tell me what the first script does.
How does that script know on what row to enter a x in column C

But again if your looking for the fastest script you probable need some one else for the best answer.
When your concerned with seconds or milliseconds then I'm not the one with the fastest answer I'm sure

So tell me what rows you want deleted lets skip the part about x's in column c
We do not need that part
 
Upvote 0
Here is a example of how a loop works:

I want you to put you name Pwill in column A many times

So say put Pwill in Row 2 column A
Put Sam in row 2 column A
and on and on for maybe 20 rows
Then when the loop runs if it finds the value Pwill in column A it will put Smith in column B

Code: 
Sub My_Loop()
'Modified 7/21/2018 11:43 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = "Pwill" Then Cells(i, 2).Value = "Smith"
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Ok, I have expanded the data for use on sheet5 and I want to run the code from a command button on sheet2 if possible​

Column A is the Ref for rows to be deleted. If the data in column E does not match column A then that row needs to be deleted. "A:K"

[TABLE="width: 626"]
<tbody>[TR]
[TD][/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]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/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"]Header[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]Header[/TD]
[TD="align: center"]Header[/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"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]-23[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.
 
Upvote 0
My Aswer Is This said:
Here is a example of how a loop works:

I want you to put you name Pwill in column A many times

So say put Pwill in Row 2 column A
Put Sam in row 2 column A
and on and on for maybe 20 rows
Then when the loop runs if it finds the value Pwill in column A it will put Smith in column B

Code: 
Sub My_Loop()
'Modified 7/21/2018 11:43 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = "Pwill" Then Cells(i, 2).Value = "Smith"
Next
Application.ScreenUpdating = True
End Sub
Click to expand...

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
 
Upvote 0
Yes your quite right I did mean entire row and faster would be great faster the better as far as I'm concerned but more importantly doing what its supposed to do and with out screen flickering :)
 
Last edited:
Upvote 0
Is there a certain place to add Screen Updating for better results or does it not really matter? I Usually add it at the top of my code after the Dim Statements
 
Upvote 0
The script looks to see what is the last row in column A with data

for i = 1 to lastrow

means from row 1 to the lastrow with data

i,1 means we look down column 1 or column A how ever you write it.


so 1 or A means the same

So how long did this script take to run?

Again like I said speed is another matter.
If your concerned about speed meaning 2.3 milliseconds versus 2.6 milliseconds

Then I'm not the one to get help from
There are 10 or more ways to do almost any thing using Excel.
 
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
93
Skyybot
Skyybot
E
  • Solved
Delete Row if
Replies
6
Views
160
Eric Penfold
E

Forum statistics

Threads
1,223,979
Messages
6,175,757
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