Excel VBA - 扫描范围并删除空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19537487/
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
Excel VBA - Scan range and delete empty rows
提问by Morten Laustsen
I have a spreadsheet that populates rows based on data from a pivot table (imported through ODBC). I'm using VLOOKUP, for example:
我有一个电子表格，它根据数据透视表（通过 ODBC 导入）中的数据填充行。我正在使用 VLOOKUP，例如：
=VLOOKUP(A8;Data!B1:I298;2;FALSE)
The result is something like
结果是这样的
Name1
Name2
Address1
Address2
Postalcode
Country
It might happen that some of the pivot columns are empty, resulting in
可能会发生一些数据透视列是空的，导致
Name1
0
Address1
0
Postalcode
0
What I need is some sort of function that loops through a range of rows, for example A8 - A14 and delete the rows that are "empty". My problem is that the rows are not truly empty, they still return 0 and contain the VLOOKUP formula.
我需要的是某种循环遍历一系列行的函数，例如 A8 - A14 并删除“空”的行。我的问题是这些行并不是真正的空，它们仍然返回 0 并包含 VLOOKUP 公式。
Is this achievable somehow? I hope I'm making sense.
这可以以某种方式实现吗？我希望我说得有道理。
Thanks.
谢谢。
回答by
Example
例子
with the code
用代码
Sub Delete0s()
Dim i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
If Range("A" & i) = 0 Then
Range("A" & i).Delete shift:=xlUp
End If
Next i
End Sub
deletes 0s so the result
删除 0s 所以结果
achieve the same result using autofilter which is normally a bit faster than looping
使用自动过滤器获得相同的结果，这通常比循环快一点
code
代码
Sub Delete0s()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("A1:A" & lastRow)
With rng
.AutoFilter Field:=1, Criteria1:="=0"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
End Sub
result
结果
