$20 USD - Delete rows based off of multiple criteria - VBA.

jlsprink · Aug 21st 2019

Hi All,

Looking to delete rows in a spreadsheet based off of values that are present in three different columns.
In layman's terms, here is what I'm trying to accomplish.

If Column B does not have a value of "BONY01" and values in Column O = 0 and Column P = 0, then delete that row.

In other words, I only want to view records of BONY01 that have a value other than 0 in either Column O or P or both. Everything else should be deleted.

Spreadsheet may have several thousand rows.

I've attached a spreadsheet with an original tab and a desired results tab for clarification as well.

Sent 10% to Oz..... Transaction ID 4N645467MA471815X Thanks in advance for the assist.

**KjBox** · Aug 21st 2019

I can look at this for you

jlsprink · Aug 21st 2019

Super! Thanks!!

**Ger Plante** · Aug 21st 2019

Do you actually want to delete the rows, or just "hide" them...?

Edit: Sorry KjBox, go ahead. I'm too slow... as usual

**KjBox** · Aug 21st 2019

I have a solution for you, I will PM you with payment details.

**KjBox** · Aug 21st 2019

PM sent, I will post code here upon receipt of payment.

**KjBox** · Aug 21st 2019

Payment received, many thanks

Here is the code, change sheet name for your actual workbook sheet name where indicated

Code

Sub DeleteRows()
    Dim x, i As Long
    
    With Sheets("Original").Cells(1).CurrentRegion '// CHANGE SHEET NAME TO SUIT
        x = .Value
        For i = 2 To UBound(x, 1)
            If x(i, 2) <> "BONY01" Then
                x(i, 2) = vbNullString
                GoTo Nxt
            End If
            If x(i, 15) = 0 And x(i, 16) = 0 Then
                x(i, 2) = vbNullString
            End If
Nxt:
        Next
        .Value = x
        .Columns(2).SpecialCells(4).EntireRow.Delete
    End With
    
End Sub

Display More

jlsprink · Aug 21st 2019

Perfect! Thanks so much!!

**KjBox** · Aug 21st 2019

You're welcome

$20 USD - Delete rows based off of multiple criteria - VBA.

Participate now!

Share