Delete Row if Criteria is met

[JakariKryze]

I have seen a few other posts similar to this, but not similar enough that i can adapt it at my current skill level. I need a VBA code that will format a page and Delete all rows with "PayPal: DPAGNF - Nutrition Dieteti" in column "C". here is what i have so far. thanks for helping!

Sub FormatBanner()
Dim Cell As Range
For Each cell In Worksheets("Banner").Range("C2:C300")
If cell.Value = "PayPal: DPAGNF - Nutrition Dieteti" Then
Cell.EntireRow.Delete
End If
Next cell

End Sub

Date	A41585	125453​	102900​
12/10/2021 23:59​	FC109074	EMV BANK CARD DEPOSIT	971.5​	Credit
12/10/2021 23:59​	FI040604	PCARD DEPOSIT Chocolate Facto	205.07​	Credit
12/9/2021 23:59​	FC108968	EMV BANK CARD DEPOSIT	606.58​	Credit
12/9/2021 23:59​	FI040530	PCARD DEPOSIT Aggie Chocolate Facto	904.67​	Credit
12/8/2021 23:59​	FC108938	PayPal: DPAGNF - Nutrition Dieteti	13.86​	Credit
12/8/2021 23:59​	FC108938	PayPal: DPAGNF - Nutrition Dieteti	66.18​	Credit
12/9/2021 12:00​	J0307182	Reverse J0306195	5525​	Credit
12/8/2021 23:59​	FC108880	EMV BANK CARD DEPOSIT	361.84​	Credit
12/8/2021 23:59​	FI040479	PCARD DEPOSIT Chocolate Facto	561.19​	Credit
12/7/2021 23:59​	FC108848	PayPal: DPAGNF - Nutrition Dieteti	162.64​	Credit

 

[Joe4]

When deleting rows on-the-fly, you need to work through your range backwards.
Here is one way:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
   
'   Loop through all rows backwards
    For r = lr To 2 Step -1
'       Check column C
        If Cells(r, "C").Value = "PayPal: DPAGNF - Nutrition Dieteti" Then Rows(r).Delete
    Next r

    Application.ScreenUpdating = True

End Sub

 

[JakariKryze]

When deleting rows on-the-fly, you need to work through your range backwards.
Here is one way:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
  
    Application.ScreenUpdating = False
  
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
  
'   Loop through all rows backwards
    For r = lr To 2 Step -1
'       Check column C
        If Cells(r, "C").Value = "PayPal: DPAGNF - Nutrition Dieteti" Then Rows(r).Delete
    Next r

    Application.ScreenUpdating = True

End Sub

Thank you very much!
Can you explain to me the need of the application.screenupdating= false/true i have never seen that before and would like to know more.

 

[Joe4]

Can you explain to me the need of the application.screenupdating= false/true i have never seen that before and would like to know more.

Sure. There actually is no "need" for it. It just suppresses all the screen flickering, as it waits until the end to refresh the screen.
This usually also helps it run a little faster.

You can use this in most codes where you are making a bunch of updated to the data (especially in loops!).
The key is to turn it off at the beginning of the code, let the code run with all the updates, then turn it back on at the end.

 

[APirlo]

Sure. There actually is no "need" for it. It just suppresses all the screen flickering, as it waits until the end to refresh the screen.
This usually also helps it run a little faster.

You can use this in most codes where you are making a bunch of updated to the data (especially in loops!).
The key is to turn it off at the beginning of the code, let the code run with all the updates, then turn it back on at the end.

Hi guys

I'm new to the world of vba .
How could I adapt the code to loop through multiple sheets?

I have multiple sheets with the same structure and with a C column named [Values]. I want that when finding the value 0 in column C it deletes the whole row and this loop is done on all sheets.

Below I'll give you the code that I'm trying to implement but obviously it didn't work

I would appreciate if you could give me some idea.

Sub Delete()

Dim lastrow As Long
Dim i As Long
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet1 (2)", "Sheet1 (3)"))
Dim ws As Worksheet

    For Each ws In sheetsArray
        lastrow = sheetsArray.Range("C" & Rows.Count).End(xlUp).Row
    
            For i = lastrow To 1 Step -1
                If sheetsArray.Range("C" & i) = 0 Then
                    sheetsArray.Range("C" & i).EntireRow.Delete
                End If
            Next i
    Next ws
End Sub

Thank you in advance!

 

[Joe4]

Welcome to the Board!

You were close! Your sheet references should be "ws", not "sheetsArray", which is the whole array of sheets.

Try this:

Sub MyDelete()

Dim lastrow As Long
Dim i As Long
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet1 (2)", "Sheet1 (3)"))
Dim ws As Worksheet

    For Each ws In sheetsArray
        lastrow = ws.Range("C" & Rows.Count).End(xlUp).Row
    
            For i = lastrow To 1 Step -1
                If ws.Range("C" & i) = 0 Then
                    ws.Range("C" & i).EntireRow.Delete
                End If
            Next i
    Next ws
End Sub

Also, do NOT use reserved words (words already used by Excel/VBA for existing functions, properties, and methods) like "Delete" for the names of procedures, functions, or variables.
Doing so can cause errors and unexpected results.

 

[APirlo]

It works perfectly!
Thanks for the tips and the code obviously