删除列包含 0 的整行，Excel 2007 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10196338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting entire row whose column contains a 0, Excel 2007 VBA
提问by CarbonD1225
UPDATE:
更新：
Alright, so i used the following code and it does what i need it to do, i.e check if the value is 0 and if its is, then delete the entire row. However i want to do this to multiple worksheets inside one workbook, one at a time. What the following code is doing is that it removes the zeros only from the current spreadsheet which is active by default when you open excel through the VBA script. here the working zero removal code:
好的，所以我使用了下面的代码，它完成了我需要它做的事情，即检查值是否为 0，如果是，则删除整行。但是，我想对一个工作簿中的多个工作表执行此操作，一次一个。以下代码所做的是仅从当前电子表格中删除零，当您通过 VBA 脚本打开 excel 时，该电子表格默认处于活动状态。这里的工作零删除代码：
Dim wsDCCTabA As Excel.Worksheet
Dim wsTempGtoS As Excel.Worksheet
Set wsDCCTabA = wbDCC.Worksheets("Login")
Set wsTempGtoS = wbCalc.Worksheets("All_TemporaryDifferences")
Dim LastRow As Long, n As Long
LastRow = wsTempGtoS.Range("E65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 5).Value = 0 Then
Cells(n, 5).EntireRow.Delete
End If
Next
What am i doing wrong? when i do the same thing for another worksheet inside the same workbook it doesnt do anything. I am using the following code to remove zeros from anohter worksheet:
我究竟做错了什么？当我对同一个工作簿中的另一个工作表做同样的事情时，它什么也不做。我正在使用以下代码从另一个工作表中删除零：
Set wsPermGtoS = wbCalc.Worksheets("All_PermanentDifferences")
'delete rows with 0 description
Dim LastRow As Long, n As Long
LastRow = wsPermGtoS.Range("E65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 5).Value = 0 Then
Cells(n, 5).EntireRow.Delete
End If
Next
Any thoughts? or another way of doing the same thing?
有什么想法吗？或做同样事情的另一种方式？
ORIGINAL QUESTION:
原问题：
I want to delete all the rows which have a zero in a particular column. I am using the following code but nothing seems to happen:
我想删除在特定列中具有零的所有行。我正在使用以下代码，但似乎没有任何反应：
CurrRow = (Range("E65536").End(xlUp).Row)
For Count = StartRow To CurrRow
If wsDCCTabA.Range("E" & Count).Value = "0" Then
wsDCCTabA.Rows(Count).Delete
End If
Next
StartRowcontains the starting row value
CurrRowcontains the row value of the last used row
StartRow包含起始行值
CurrRow包含最后使用的行的行值
回答by tpascale
See if this helps:
看看这是否有帮助：
Sub DelSomeRows()
Dim colNo As Long: colNo = 5 ' hardcoded to look in col 5
Dim ws As Worksheet: Set ws = ActiveSheet ' on the active sheet
Dim rgCol As Range
Set rgCol = ws.Columns(colNo) ' full col range (huge)
Set rgCol = Application.Intersect(ws.UsedRange, rgCol) ' shrink to nec size
Dim rgZeroCells As Range ' range to hold all the "0" cells (union of disjoint cells)
Dim rgCell As Range ' single cell to iterate
For Each rgCell In rgCol.Cells
If Not IsError(rgCell) Then
If rgCell.Value = "0" Then
If rgZeroCells Is Nothing Then
Set rgZeroCells = rgCell ' found 1st one, assign
Else
Set rgZeroCells = Union(rgZeroCells, rgCell) ' found another, append
End If
End If
End If
Next rgCell
If Not rgZeroCells Is Nothing Then
rgZeroCells.EntireRow.Delete ' deletes all the target rows at once
End If
End Sub
回答by Alpha
Once you delete a row, u need to minus the "Count" variable
删除一行后，您需要减去“计数”变量
CurrRow = (Range("E65536").End(xlUp).Row)
For Count = StartRow To CurrRow
If wsDCCTabA.Range("E" & Count).Value = "0" Then
wsDCCTabA.Rows(Count).Delete
' Add this line:
Count = Count - 1
End If
Next
回答by CarbonD1225
I got it. For future reference, i used
我知道了。为了将来参考，我使用了
ActiveWorkbook.Sheets("All_temporaryDifferences").Activate
and
和
ActiveWorkbook.Sheets("All_Permanentdifferences").Activate
回答by D_Bester
You don't need to use ActiveWorkbook.Sheets("All_temporaryDifferences").Activate. In fact if the ActiveWorkbookis different from wbCalcyou would get an error.
您不需要使用ActiveWorkbook.Sheets("All_temporaryDifferences").Activate. 事实上，如果与您ActiveWorkbook不同，wbCalc则会出现错误。
Your real problem is that you are using an unqualified reference to Cells(n, 5).Value. Unqualified means that you aren't specifying which sheet to use so it defaults to the active sheet. That may work sometimes but it is poor code. In your case it didn't work.
您真正的问题是您正在使用对Cells(n, 5).Value. 不合格意味着您没有指定要使用的工作表，因此它默认为活动工作表。这有时可能有效，但代码很差。在你的情况下它没有用。
Instead you should always use qualified references. wsTempGtoS.Cells(n, 5).Valueis a qualified reference. wsTempGtoSspecifies which worksheet you want so VBA is not left guessing.
相反，您应该始终使用限定的引用。wsTempGtoS.Cells(n, 5).Value是一个合格的参考。wsTempGtoS指定您想要的工作表，这样 VBA 就不会被猜测。
Dim LastRow As Long, n As Long
LastRow = wsTempGtoS.Range("E65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If wsTempGtoS.Cells(n, 5).Value = 0 Then
wsTempGtoS.Cells(n, 5).EntireRow.Delete
End If
Next
This: CurrRow = (Range("E65536").End(xlUp).Row)is also an unqualified reference. Instead it should be CurrRow = wsDCCTabA.Range("E65536").End(xlUp).Row.
这：CurrRow = (Range("E65536").End(xlUp).Row)也是一个不合格的参考。相反，它应该是CurrRow = wsDCCTabA.Range("E65536").End(xlUp).Row。
