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]

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

 

[tonyyy]

Thanks tonyyy, I will give that a try and have a look at the link you provided

Much appreciated

pwill

You're very welcome...

Another .AutoFilter approach...

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

 

[pwill]

Here is a filter version:

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

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

 

[pwill]

You're very welcome...

Another .AutoFilter approach...

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

Thanks Tonyyy,

This is much better than my method

pwill

 

[My Aswer Is This]

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

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

 

[Peter_SSs]

Or if anyone knows a quicker way to delete Range, rows where column C has an X would be great?

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.

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

 

[pwill]

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

 

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

I would also be interested to know ..
- approximately how many rows of data altogether
- approximately what proportion of x's there are

 

[pwill]

Hi Peter_Sss, Around 8000 at a time

 

[Peter_SSs]

Hi Peter_Sss, Around 8000 at a time

Thanks. That answers the first question, what about my other one?

I would also be interested to know ..
<del>- approximately how many rows of data altogether</del>
- approximately what proportion of x's there are