删除当前行VBA

Question

问题：

我需要删除不在我的条件的行。

代码：

假定所有的值被初始化。

For ind = 2 To lrow 
    reportProcess = Trim(LTrim(RTrim(ws.Range("G" & ind).Text))) 
    If reportProcess = "Fulfillment - H/W Direct Customers (Operational)" Or reportProcess = "Revenue - Hardware" Then 
     brand = "HW" 
    ElseIf reportProcess = "Revenue - Software" Then 
     ' ~~ Check Control Point Number 
     subReportProcess = Mid(ws.Range("I" & ind).Text, 12, 3) 
     If subReportProcess = "201" Or subReportProcess = "202" Or subReportProcess = "203" Or subReportProcess = "204" Or subReportProcess = "205" Then 
      brand = "PBS" 
     Else 
      brand = "SWG" 
     End If 
    ElseIf reportProcess = "Revenue - GBS" Or reportProcess = "Revenue - GTS IS" Or reportProcess = "Fulfillment - Services(Operational)" Then 
     brand = "PBS" 
    ElseIf reportProcess = "Revenue - TSS" Then 
     brand = "TSS" 
    ElseIf reportProcess = "Accounts Receivable" Or reportProcess = "IBM Credit LLC - Accounts Receivable" Then 
     brand = "AR" 
    End If 

    country = Trim(LTrim(RTrim(ws.Range("V" & ind).Text))) 
    If country = "Taiwan" Then 
     geo = "Taiwan" 
    ElseIf country = "India" Then 
     geo = "India" 
    ElseIf country = "New Zealand" Or country = "Australia" Then 
     geo = "ANZ" 
    ElseIf country = "Hong Kong" Then 
     geo = "Hong Kong" 
    ElseIf country = "Philippines" Or country = "Malaysia" Or country = "Singapore" Or country = "Thailand" Or country = "Vietnam" Or country = "Indonesia" Then 
     geo = "ASEAN" 
    Else 
    ' ~~ INSERT DELETE ROW HERE 
    End If 
    ws.Range("B" & ind) = geo 
    ws.Range("A" & ind) = brand 
Next ind 

我的尝试：

ActiveCell.EntireRow.Delete - 不过这将删除所有行

Selection.EntireRow.Delete - 如上

我如何使用循环来删除特定的行号相同的结果？

Answer 1

您将需要遍历列表从最后一行到第一行。如果你不这样做，你会跳过删除行后的下一行。注意：这也适用于从列表框，组合框和集合中删除项目。

For ind = lrow To 2 Step -1 
    reportProcess = Trim(LTrim(RTrim(ws.Range("G" & ind).Text))) 
    If reportProcess = "Fulfillment - H/W Direct Customers (Operational)" Or reportProcess = "Revenue - Hardware" Then 
     brand = "HW" 
    ElseIf reportProcess = "Revenue - Software" Then 
     ' ~~ Check Control Point Number 
     subReportProcess = Mid(ws.Range("I" & ind).Text, 12, 3) 
     If subReportProcess = "201" Or subReportProcess = "202" Or subReportProcess = "203" Or subReportProcess = "204" Or subReportProcess = "205" Then 
      brand = "PBS" 
     Else 
      brand = "SWG" 
     End If 
    ElseIf reportProcess = "Revenue - GBS" Or reportProcess = "Revenue - GTS IS" Or reportProcess = "Fulfillment - Services(Operational)" Then 
     brand = "PBS" 
    ElseIf reportProcess = "Revenue - TSS" Then 
     brand = "TSS" 
    ElseIf reportProcess = "Accounts Receivable" Or reportProcess = "IBM Credit LLC - Accounts Receivable" Then 
     brand = "AR" 
    End If 

    country = Trim(LTrim(RTrim(ws.Range("V" & ind).Text))) 
    If country = "Taiwan" Then 
     geo = "Taiwan" 
    ElseIf country = "India" Then 
     geo = "India" 
    ElseIf country = "New Zealand" Or country = "Australia" Then 
     geo = "ANZ" 
    ElseIf country = "Hong Kong" Then 
     geo = "Hong Kong" 
    ElseIf country = "Philippines" Or country = "Malaysia" Or country = "Singapore" Or country = "Thailand" Or country = "Vietnam" Or country = "Indonesia" Then 
     geo = "ASEAN" 
    Else 
     ' ~~ INSERT DELETE ROW HERE 
     ws.Rows(ind).Delete Shift:=xlUp 
    End If 
    ws.Range("B" & ind) = geo 
    ws.Range("A" & ind) = brand 
Next ind 

---

我重构每@ShaiRado咨询OP的代码。

For ind = lrow To 2 Step -1 
    reportProcess = Trim(LTrim(RTrim(ws.Range("G" & ind).Text))) 
    Select Case reportProcess 
    Case "Fulfillment - H/W Direct Customers (Operational)", "Revenue - Hardware" 
     brand = "HW" 
    Case "Revenue - Software" 
     ' ~~ Check Control Point Number 
     subReportProcess = Mid(ws.Range("I" & ind).Text, 12, 3) 
     If subReportProcess = "201" Or subReportProcess = "202" Or subReportProcess = "203" Or subReportProcess = "204" Or subReportProcess = "205" Then 
      brand = "PBS" 
     Else 
      brand = "SWG" 
     End If 
    Case "Revenue - GBS", "Revenue - GTS IS", "Fulfillment - Services(Operational)" 
     brand = "PBS" 
    Case "Revenue - TSS" 
     brand = "TSS" 
    Case "Accounts Receivable", "IBM Credit LLC - Accounts Receivable" 
     brand = "AR" 
    End Select 

    country = Trim(LTrim(RTrim(ws.Range("V" & ind).Text))) 
    Select Case country 
    Case "Taiwan" 
     geo = "Taiwan" 
    Case "India" 
     geo = "India" 
    Case "New Zealand", "Australia" 
     geo = "ANZ" 
    Case "Hong Kong" 
     geo = "Hong Kong" 
    Case "Philippines", "Malaysia", "Singapore", "Thailand", "Vietnam", "Indonesia" 
     geo = "ASEAN" 
    Case Else 
     ' ~~ INSERT DELETE ROW HERE 
     ws.Rows(ind).Delete Shift:=xlUp 
    End Select 

    ws.Range("B" & ind) = geo 
    ws.Range("A" & ind) = brand 
Next ind 

Answer 2

试试下面的代码：

For ind = lrow To 2 Step -1 
ws.Range("G" & ind).select 
reportProcess = Trim(LTrim(RTrim(ws.Range("G" & ind).Text))) 
If reportProcess = "Fulfillment - H/W Direct Customers (Operational)" Or reportProcess = "Revenue - Hardware" Then 
    brand = "HW" 
ElseIf reportProcess = "Revenue - Software" Then 
    ' ~~ Check Control Point Number 
    subReportProcess = Mid(ws.Range("I" & ind).Text, 12, 3) 
    If subReportProcess = "201" Or subReportProcess = "202" Or subReportProcess = "203" Or subReportProcess = "204" Or subReportProcess = "205" Then 
     brand = "PBS" 
    Else 
     brand = "SWG" 
    End If 
ElseIf reportProcess = "Revenue - GBS" Or reportProcess = "Revenue - GTS IS" Or reportProcess = "Fulfillment - Services(Operational)" Then 
    brand = "PBS" 
ElseIf reportProcess = "Revenue - TSS" Then 
    brand = "TSS" 
ElseIf reportProcess = "Accounts Receivable" Or reportProcess = "IBM Credit LLC - Accounts Receivable" Then 
    brand = "AR" 
End If 

country = Trim(LTrim(RTrim(ws.Range("V" & ind).Text))) 
If country = "Taiwan" Then 
    geo = "Taiwan" 
ElseIf country = "India" Then 
    geo = "India" 
ElseIf country = "New Zealand" Or country = "Australia" Then 
    geo = "ANZ" 
ElseIf country = "Hong Kong" Then 
    geo = "Hong Kong" 
ElseIf country = "Philippines" Or country = "Malaysia" Or country = "Singapore" Or country = "Thailand" Or country = "Vietnam" Or country = "Indonesia" Then 
    geo = "ASEAN" 
Else 
    'Delete the row 
    activecell.entirerow.delete 
    ind = ind - 1 
    goto GotoNextRecord 
End If 
ws.Range("B" & ind) = geo 
ws.Range("A" & ind) = brand 
    GotoNextRecord: 
    Next ind