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

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

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. That answers the first question, what about my other one?

There's not much difference around 8000 rows and around 500 rows left after filter

 

[Peter_SSs]

There's not much difference around 8000 rows and around 500 rows left ..

OK, so about 90% of rows are removed.

.. after filter

You original requirement was to delete rows, not filter them wasn't it?


I take it that you haven't had a chance to try the post 16 code yet?

 

[pwill]

Not yet Peter_Sss, on my way home now so in an hour or 2 I will try it out

 

[My Aswer Is This]

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

I do not understand this script. When I try it deletes a lot more rows then just those with a x in column 3 which was the original question from my understanding. I see nothing in this script which even mentions a x

 

[MARK858]

I do not understand this script. When I try it deletes a lot more rows then just those with a x in column 3 which was the original question from my understanding. I see nothing in this script which even mentions a x

The OP only has X's and blanks in column C and so the code is using the fact that the other cells are blank to only delete the rows with any constants in column C.

The sort is there so it only has to delete one contiguous block.

Hope that helps.

 

[Peter_SSs]

When I try it deletes a lot more rows then just those with a x in column 3 which was the original question from my understanding.

I tested with the sample data from post 1 and also other sheets with similar data repeated for 2000 rows and for 10000 rows. It only deleted the rows with x in column C.

I see nothing in this script which even mentions a x

That would be

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]

The OP only has X's and blanks in column C and so the code is using the fact that the other cells are blank to only delete the rows with any constants in column C.

The sort is there so it only has to delete one contiguous block.

Hope that helps.

What is the OP?

 

[Peter_SSs]

What is the OP?

"Original Poster" (ie you) or sometimes "Original Post" (ie post #1 in the thread)

 

[My Aswer Is This]

I tested with the sample data from post 1 and also other sheets with similar data repeated for 2000 rows and for 10000 rows. It only deleted the rows with x in column C.



That would be

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

I just tried it again and it did not work.
so your saying xlconstants means cells with a x
so if we wanted to delete cells with only a b it would blconstants

 

[My Aswer Is This]

Well in the first post I just thought from the subject title the original poster was wanting to learn about loops.
But now since speed is more the issue.
And since maybe using a Filter was slower then some others maybe this would be faster.

I never check my scripts for speed.

Sub Delete_Empty()
'Modified  7/21/2018  7:20:12 AM  EDT
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C1").Resize(Lastrow).Replace What:="x", Replacement:=""
Columns(3).SpecialCells(xlBlanks).EntireRow.Delete
End Sub