Deleting rows and move on

[RZoig]

Hello,

I couldn't find a matching solution to my problem, so here I am asking fro help.

I have a sheet with complex formatted data with some zero values in an evaluation column.
I want to delete lines from that information when a evaluation column has zero value.
Since it is rather complex, using filters won't work.

I want to select an initial cell from evaluation column and run my code until it reaches a cell with 'STOP'.

Can somebody help?

This is a simplified example.
I have this:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]Op1[/TD]
[TD="class: xl63, width: 64"] Op2[/TD]
[/TR]
[TR]
[TD]WAVE 1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]WAVE 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]WAVE 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]WAVE 4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]WAVE 5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]WAVE 6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]WAVE 7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]WAVE 8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"] STOP[/TD]
[/TR]
</tbody>[/TABLE]

I choose manually cell just under Op2, run my code and end with this:

[TABLE="width: 238"]
<tbody>[TR]
[TD][/TD]
[TD]Op1[/TD]
[TD] Op2[/TD]
[/TR]
[TR]
[TD]WAVE 3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]WAVE 4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]WAVE 5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]WAVE 6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]WAVE 8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] STOP[/TD]
[/TR]
</tbody>[/TABLE]

Zero Values in Column Op2 are gone.

Thank you for your help.
Rui

 

[Joe4]

When you select a cell in your column, are you only evaluating the cells starting at that cell, down to the "STOP" cell, or are you checking cells above it too (and if so, how far up do you go)?

 

[RZoig]

Always down.

I have titles and stuff that must be kept untouched.
And there are several iterations along the sheet thus the stop cell.

 

[Joe4]

Try this:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Get row and column of active cell
    r = ActiveCell.Row
    c = ActiveCell.Column

'   Find last row with data in c
    lr = Cells(Rows.Count, c).End(xlUp).Row

'   Loop through all rows until you hit "STOP"
    Do Until Cells(r, c) = "STOP"
'       Delete row if value is 0 (and not blank)
        If (Len(Cells(r, c))) > 0 And (Cells(r, c) = 0) Then
            Rows(r).Delete
        Else
'           ...otherwise move down one row
            r = r + 1
        End If
'       Exit if you hit the last row in the workbook
        If r > lr Then Exit Do
    Loop

    Application.ScreenUpdating = True
    
    MsgBox "Process Complete!"
    
End Sub

 

[RZoig]

awesome!

It worked perfect.
Thanks a lot Joe4!!

 

[RZoig]

Just a question.

Is this part of the code necessary:
' Find last row with data in c
lr = Cells(Rows.Count, c).End(xlUp).Row

or it's just a safety procedure? Preventing an endless search?

 

[Joe4]

You are welcome.

Just a question.

Is this part of the code necessary:
' Find last row with data in c
lr = Cells(Rows.Count, c).End(xlUp).Row

or it's just a safety procedure? Preventing an endless search?

Exactly. As you can see further down in the code, I use it to empty out of the loop.
This would protect in the case where you select a cell that does not have a "STOP" anywhere below it.