Excel VBA delete rows based on multiple column criteria

Question

I am trying to do some cuts to a sheet of data based on if a row meets 2 criteria in different columns, i.e. if the value in column D is > -2 and if the value in the adjacent cell of column F is > -2 or NA, then delete the entire row. If only 1 or none of the criteria is met then it should keep the row. Below is what i have so far. When i run the macro, it will go on forever, but I don't see how this should be since it doesn't look like an endless loop to me (to be fair i have only let it sit for 45 minutes, but there is only around 15,000 data rows so it shouldn't take longer than 10 minutes realistically). Any help would be greatly appreciated.

Sub Cuts()
    Dim wb1 As Workbook, sh1 As Worksheet
    Dim lastrow1 As Long, lastrow2 As Long
    Set wb1 = Workbooks(“ovaryGisticARRAYRNAseq.final.xlsx")
    Set sh1 = wb1.Sheets(“Cuts”)
    lastrow1 = sh1.Cells(Rows.Count, 4).End(xlUp).Row
    lastrow2 = sh1.Cells(Rows.Count, 6).End(xlUp).Row
    For i = 1 To lastrow1
        For j = 1 To lastrow2
            If sh1.Cells(i, 4).Value > -2 Then
                If sh1.Cells(j, 6).Value > -2 Then
                    sh1.Cells(j, 6).EntireRow.Delete
                ElseIf sh1.Cells(j, 6).Value = “NA” Then
                    sh1.Cells(j, 6).EntireRow.Delete
                End If
            End If
        Next j
    Next i
End Sub

When deleting rows in a For... Next loop, always start at the bottom and work upwards; e.g. For i = lastrow1 to 1 Step -1 — user4039065
– user4039065, Commented Sep 15, 2014 at 23:47
@pnuts - filtering would be likely be faster but the OP wanted to include row 1 as a possible deletion and the lack of a header row concerned me. — user4039065
– user4039065, Commented Sep 16, 2014 at 21:35

score 2 · Accepted Answer · 2014-09-16 00:07:28Z

2

I'm not sure how you want to handle blank cells or text in column headings but I would propose this modification.

Sub Cuts()
    Dim wb1 As Workbook
    Dim lr As Long, i As Long
    Set wb1 = Workbooks(“ovaryGisticARRAYRNAseq.final.xlsx")
    With wb1.Sheets("Cuts")
        lr = Application.Max(.Cells(Rows.Count, 4).End(xlUp).Row, _
          .Cells(Rows.Count, 6).End(xlUp).Row)
        For i = lr To 1 Step -1
            If .Cells(i, 4).Value > -2 And _
              (.Cells(i, 6).Value > -2 Or UCase(.Cells(i, 6).Value) = "NA") Then
                .Rows(i).EntireRow.Delete
            End If
        Next i
    End With
End Sub

Collectives™ on Stack Overflow

Excel VBA delete rows based on multiple column criteria

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related