删除当前行VBA
问题描述：
问题：删除当前行VBA
我需要删除不在我的条件的行。
代码：
假定所有的值被初始化。
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 = "*" Then
geo = "*"
ElseIf country = "India" Then
geo = "India"
ElseIf country = "New Zealand" Or country = "Australia" Then
geo = "ANZ"
ElseIf country = "*" Then
geo = "*"
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 - 如上
我如何使用循环来删除特定的行号相同的结果？
答
您将需要遍历列表从最后一行到第一行。如果你不这样做，你会跳过删除行后的下一行。注意：这也适用于从列表框，组合框和集合中删除项目。
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 = "*" Then
geo = "*"
ElseIf country = "India" Then
geo = "India"
ElseIf country = "New Zealand" Or country = "Australia" Then
geo = "ANZ"
ElseIf country = "*" Then
geo = "*"
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 "*"
geo = "*"
Case "India"
geo = "India"
Case "New Zealand", "Australia"
geo = "ANZ"
Case "*"
geo = "*"
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
+0
哦，我明白了，所以'行（ind）.EntireRow.Delete'将不起作用。谢谢！这解决了我的问题:) – Hibari
+1
@Thomas Inzina欢迎回来:)但是，你比这更好，这个代码是“尖叫”升级到“Select Case”（而不是这个混乱的'如果'和'Or' ）。 –
+1
@ShaiRado哈哈..你是对的。我应该使用“Select Case”重构它，或者只是显示修改。代码已更新。再次感谢 – 2017-08-02 07:14:44
答
试试下面的代码：
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 = "*" Then
geo = "*"
ElseIf country = "India" Then
geo = "India"
ElseIf country = "New Zealand" Or country = "Australia" Then
geo = "ANZ"
ElseIf country = "*" Then
geo = "*"
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
你想删除哪一行？ –
@RachelChia循环中当前的一个，所以如果'ind = 2'且条件不匹配，请将其删除。 – Hibari
你想删除整个第二行或第一列第二行？如果你想删除特定的行例子：ws.Range（“A”＆ind）。删除或整行：行（ind）.EntireRow.Delete –