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

 

[My Aswer Is This]

Here is a script for your first question:

Sub Delete_Rows()
'Modified 7/20/2018 6:40 PM  EDT
Application.ScreenUpdating = False
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, "C").Value = "x" Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub

 

[tonyyy]

pwill,

There are many sources on the web to introduce you to loops. Just one example... Excel VBA Programming Loops.

When deleting rows, it's best to loop from the last row to the first row otherwise the row index number gets confused...

Sub Macro3()
Dim Sht1 As Worksheet: Set Sht1 = Sheet1
Dim lRow As Long
Dim i As Long

lRow = Sht1.Cells(Rows.Count, "A").End(xlUp).Row
For i = lRow To 2 Step -1
    If Cells(i, 3).Value = "x" Then Rows(i).Delete
Next i
End Sub

Cheers,

tonyyy

 

[pwill]

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

 

[pwill]

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

Much appreciated

pwill

 

[My Aswer Is This]

So you tried my script and it's too slow. Is that what your saying.
How many rows are you dealing with?

We could use a Filter if there are lots of rows which might be faster
I thought you were wanting to learn about loops.

 

[pwill]

Hi My Answer Is This,

Thanks, I will give that a try also

much appreciated

pwill

 

[pwill]

So you tried my script and it's too slow. Is that what your saying.
How many rows are you dealing with?

We could use a Filter if there are lots of rows which might be faster
I thought you were wanting to learn about loops.

Sorry? No I havn't tried anything yet I only just seen your reply not sure what you you mean?

 

[My Aswer Is This]

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

 

[pwill]

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

Hi My Answer Is This, I see why you posted that now, I hadn't seen any reply's when I posted this, sorry for the confusion

pwill