Delete entire rows by conditions

[Panoos64]

Hi to all of you!
Kindly require to provide me support for a VBA code so that to run through columns "O", "P", 'Q" and where the rows are blank or "0" should delete entire rows. Therefore that, the rows "1" - "7" are headings and the code should run from row "8" till rows down. Thank you all in advance. Base into all of you, i overcome my tasks in a short time and my figures are accurate.

 

[Michael M]

Re: Delete entire rows by contitions

Try..Untested...

Sub MM1()
Dim lr As Long, cell As Range
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
For Each cell In Range("O8:Q" & lr)
    If cell.Value = "" Or cell.Value = 0 Then
        cell.EntireRow.Delete
    End If
Next cell
End Sub

 

[Panoos64]

Re: Delete entire rows by contitions

Thank you Michael, it works but, maybe i done a mistake in my explanation on above post. The condition which code should check is that, if the all three columns "O", "P", 'Q" in a row are blank or "0" then should delete entire row. However thank you for time

 

[Peter_SSs]

Re: Delete entire rows by contitions

About how many rows will be in your whole data?

 

[Panoos64]

Re: Delete entire rows by contitions

Hi Peter, around 500 rows. Based on my below extract, the rows which should delete are 124 and 125. Thank u Peter for your interesting to resolve my issue. Hv a great day

<colgroup><col style="mso-width-source:userset;mso-width-alt:2588;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3242; width:68pt" width="91" span="3"> </colgroup><tbody>
[TD="class: xl63, width: 73"]
[/TD]
[TD="class: xl63, width: 91"]O[/TD]
[TD="class: xl63, width: 91"]P[/TD]
[TD="class: xl63, width: 91"]Q[/TD]

[TD="class: xl64, align: right"]123[/TD]
[TD="class: xl63, align: right"]150.25
[/TD]
[TD="class: xl63"]
[/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl64, align: right"]124[/TD]
[TD="class: xl63"]
[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]0.00[/TD]

[TD="class: xl64, align: right"]125[/TD]
[TD="class: xl63"]
[/TD]
[TD="class: xl63, align: right"]0.00[/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl64, align: right"]126[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]178.29[/TD]
[TD="class: xl63"]
[/TD]

</tbody>

 

[Peter_SSs]

Re: Delete entire rows by contitions

Hi Peter, around 500 rows.

Thanks. That's a small number for vba processing so give this a try in a copy of your workbook.

Sub Delete_Rows()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("O:R").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 8 Step -1
    If Len(Replace(Join(Application.Index(Cells, r, Array(15, 16, 17)), ""), "0", "")) = 0 Then Rows(r).Delete
  Next r
  Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Re: Delete entire rows by contitions

Another aproch

Sub Delete_Rows()
  Dim lr As Long, i As Long, a(), r As Range
  Application.ScreenUpdating = False
  lr = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
  Set r = Range("A" & lr + 1)
  a = Range("O8:Q" & lr)
  For i = 1 To UBound(a)
    If (a(i, 1) = 0 Or a(i, 1) = "") And _
       (a(i, 2) = 0 Or a(i, 2) = "") And _
       (a(i, 3) = 0 Or a(i, 3) = "") Then
      Set r = Union(r, Range("A" & i + 7))
    End If
  Next i
  r.EntireRow.Delete
End Sub

 

[Trebor76]

Re: Delete entire rows by contitions

Another possibility - (just a slight twist on Peter's method):

Option Explicit
Sub Delete_Rows()
  
    Dim lngMyRow As Long
  
    Application.ScreenUpdating = False
    
    For lngMyRow = Range("O:Q").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row To 8 Step -1
        If Evaluate("SUM(O" & lngMyRow & ":Q" & lngMyRow & ")") = 0 Then Rows(lngMyRow).Delete
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Peter_SSs]

Re: Delete entire rows by contitions

Another possibility - (just a slight twist on Peter's method):

A couple of comments as to why I wouldn't go with that code. Although not existing in the small sample given by the OP, that would delete rows other than specified by where the rows are blank or "0" in at least these 2 circumstances:
- the row contained text values
- the row contained, for example, 3, -7, 4

 

[Trebor76]

Re: Delete entire rows by contitions

Thanks Peter. I based on it what the OP posted but you are totally correct.

Regards,

Robert