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

 

[MARK858]

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]

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]

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?

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.

so if we wanted to delete cells with only a b it would blconstants

I'm definitely not saying that.

 

[My Aswer Is This]

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]

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]

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]

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]

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

 

[Peter_SSs]

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

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]

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

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