Excel VBA根据多个列标准删除行 [英] Excel VBA delete rows based on multiple column criteria

问题描述

我正在尝试根据不同列中符合2个条件的一行数据进行切割，即如果列D中的值> 2，并且如果列F的相邻单元格中的值为> -2或NA，然后删除整行。如果只有1个或者没有一个条件被满足，那么它应该保留行。以下是我到目前为止。当我运行宏时，它会永远继续下去，但是我不明白这是怎么回事，因为它看起来不像是一个无休止的循环（为了公平起见，我只能让它坐45分钟，但在那里只有大约15,000个数据行，因此实际上不应该花费超过10分钟）。任何帮助将不胜感激。

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

推荐答案

我不知道你想如何处理列h中的空白单元格或文本但是我会提出这个修改。

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

这篇关于Excel VBA根据多个列标准删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！