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
Thanks My Answer Is This,

It takes me a bit longer than most to digest things but it helps me understand better in the long run, I will have to study your code in more detail and let you know how I get on :)

pwill
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Thanks tonyyy, I will give that a try and have a look at the link you provided :smile:

Much appreciated

pwill
Click to expand...

You're very welcome...

Another .AutoFilter approach...

Code: 
Sub FilterDelete()
Application.ScreenUpdating = False
With ActiveSheet
    If .AutoFilterMode = True Then .AutoFilterMode = False
    .Range("A1").AutoFilter field:=3, Criteria1:="x"
    .UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub
 
Last edited:
Upvote 0
My Aswer Is This said:
Here is a filter version:
Code: 
Sub Filter_Me_Please()
'Modified  7/20/2018  6:59:27 PM  EDT
Dim Lastrow As Long
Dim c As Long
Dim s As String
c = "3" ' Column Number Modify this to your need
s = "x" 'Saerch Value Modify to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(Lastrow)
    .AutoFilter 1, s
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Else
        MsgBox "No values found"
    End If
    .AutoFilter
End With
End Sub
Click to expand...

Hi My Answer Is This,

Thank you for this filtered version, my way was much to slow, didn't realise how slow the process would be when there are many rows to delete, your filtered version is much quicker and i will make good use of it. Still, I have learned a bit more about how to use the For and Next Statement, wouldn't say I'm there yet, small steps I'll get there in the end :)

Thanks again

much appreciated

pwill
 
Upvote 0
tonyyy said:
You're very welcome...

Another .AutoFilter approach...

Code: 
Sub FilterDelete()
Application.ScreenUpdating = False
With ActiveSheet
    If .AutoFilterMode = True Then .AutoFilterMode = False
    .Range("A1").AutoFilter field:=3, Criteria1:="x"
    .UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub
Click to expand...

Thanks Tonyyy,

This is much better than my method :)

pwill
 
Last edited:
Upvote 0
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
pwill said:
Hi My Answer Is This,

Thank you for this filtered version, my way was much to slow, didn't realise how slow the process would be when there are many rows to delete, your filtered version is much quicker and i will make good use of it. Still, I have learned a bit more about how to use the For and Next Statement, wouldn't say I'm there yet, small steps I'll get there in the end :)

Thanks again

much appreciated

pwill
Click to expand...
 
Upvote 0
pwill said:
Or if anyone knows a quicker way to delete Range, rows where column C has an X would be great?
Click to expand...
Assuming those x's are not formula results, for me this is about 10 times faster than any of the other codes posted so far.
Code: 
Sub Del_Rows()
  With Range("A2", Range("C" & Rows.Count).End(xlUp))
    .Sort Key1:=.Columns(3), Header:=xlNo
    .Columns(3).SpecialCells(xlConstants).EntireRow.Delete
  End With
End Sub
 
Upvote 0
Hi Peter_Sss,

Thanks for that, I like faster :) I will give that a try later and let you know how I get on, just at work at min.

Much appreciated

pwill
 
Upvote 0
pwill said:
Thanks for that, I like faster :) I will give that a try later and let you know how I get on, just at work at min.
Click to expand...
I would also be interested to know ..
- approximately how many rows of data altogether
- approximately what proportion of x's there are
 
Last edited:
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,970
Messages
6,175,703
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