Help with Looping (For and Next Statement)

pwill · Jul 20, 2018

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

MARK858 · Jul 21, 2018

My Aswer Is This said:

Code:

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

That would delete all the rows as the cells without the X's are blank according to the sample in post number 1.

My Aswer Is This · Jul 21, 2018

It is true the original image only showed empty cells and x's in column C
But i just thought this was a example and really did not mean all other cells in column C were empty.
He did not mention that.
So if that is the case then I'm sure Peter's script would be faster then mine.

Peter_SSs · Jul 21, 2018

My Aswer Is This said:
I just tried it again and it did not work.

Did you try it with the sample data provided, or some you made up yourself?

My Aswer Is This said:
so your saying xlconstants means cells with a x

Only if the column contains blank cells or cells with an x, which is what the OP told us they have.

My Aswer Is This said:
so if we wanted to delete cells with only a b it would blconstants

I'm definitely not saying that.

My Aswer Is This · Jul 21, 2018

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

Peter_SSs · Jul 21, 2018

My Aswer Is This said:
But i just thought this was a example and really did not mean all other cells in column C were empty.
He did not mention that.

But the OP did give sample codes that supposedly "worked" (one row at a time) and those codes made no mention of x in column C, they just found any entry in column C and deleted that row, again confirming that there are not other non-blank values in that column that need to be left undeleted.

My Aswer Is This · Jul 21, 2018

We will have to wait and see. It's sort of funny. I thought this exercise was all about learning how to use loops.

Peter_SSs · Jul 21, 2018

My Aswer Is This said:
I thought this exercise was all about learning how to use loops.

Perhaps to start with that was the only way the OP though of doing it. By post #4 that was certainly not the requirement.

pwill · Jul 21, 2018

Can I just say tonyyy's post #12 is quite fast

Peter_SSs · Jul 21, 2018

pwill said:
Can I just say tonyyy's post #12 is quite fast

To be honest, 8000 rows isn't all that big so most codes will seem pretty fast & the extra speed would only be particularly relevant if working on much larger data or if repeating on lots of sets of data this size.

However, for the record, with 10,000 rows of data where about two thirds get deleted, for my testing
- Tony's code from post 12 took 1.563 seconds
- Code from post 16 took 0.047 seconds (ie about 33 times faster)

.. and if we add in screenupdating off/on

Code:

Sub Del_Rows_v2()
  Application.ScreenUpdating = False
  With Range("A2", Range("C" & Rows.Count).End(xlUp))
    .Sort Key1:=.Columns(3), Header:=xlNo
    .Columns(3).SpecialCells(xlConstants).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
End Sub

with the same data this code took 0.023 seconds (ie nearly 68 times faster than post 12)

pwill · Jul 21, 2018

Peter_SSs said:
To be honest, 8000 rows isn't all that big so most codes will seem pretty fast & the extra speed would only be particularly relevant if working on much larger data or if repeating on lots of sets of data this size.

However, for the record, with 10,000 rows of data where about two thirds get deleted, for my testing
- Tony's code from post 12 took 1.563 seconds
- Code from post 16 took 0.047 seconds (ie about 33 times faster)

.. and if we add in screenupdating off/on

Code:

Sub Del_Rows_v2() Application.ScreenUpdating = False With Range("A2", Range("C" & Rows.Count).End(xlUp)) .Sort Key1:=.Columns(3), Header:=xlNo .Columns(3).SpecialCells(xlConstants).EntireRow.Delete End With Application.ScreenUpdating = True End Sub

with the same data this code took 0.023 seconds (ie nearly 68 times faster than post 12)

I will be home shortly, looking forward to trying out all these options, I will let u know how I get on

0.023 sec does sound impressive

pwill

Help with Looping (For and Next Statement)

pwill

Active Member

MARK858

MrExcel MVP

Excel Facts

My Aswer Is This

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

My Aswer Is This

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

pwill

Active Member

Peter_SSs

MrExcel MVP, Moderator

pwill

Active Member

Similar threads

Forum statistics

Share this page

Help with Looping (For and Next Statement)

Active Member

MrExcel MVP

Excel Facts

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock