Excel VBA - 扫描范围并删除空行 [英] Excel VBA - Scan range and delete empty rows

问题描述

我有一个电子表格根据数据透视表的数据（通过ODBC导入）填充行。我正在使用VLOOKUP，例如：

  = VLOOKUP（A8; Data！B1：I298; 2; FALSE）
  

结果类似于

  Name1 
 Name2 
 Address1 
 Address2 
 Postalcode 
国家
  

可能会发生一些枢轴列为空，导致

  Name1 
 0 
地址1 
 0 
邮政编码
 0 
  

我需要的是一些循环遍历一系列行的函数，例如A8 - A14，并删除空行。我的问题是行不是真的空，它们仍然返回0并包含VLOOKUP公式。

这可以以某种方式实现吗？我希望我有意义。

谢谢。

解决方案

示例

代码

  Sub Delete0s（）
 Dim i As Long 
 For i = Range（A& Rows.Count）.End（xlUp）.Row To 1 Step -1 
如果Range（A& i）= 0然后
 Range（A& i）.Delete shift：= xlUp 
 End If 
 Next i 
 End Sub 
  

删除0，结果

---

使用自动过滤器实现相同的结果，通常情况下比循环更快

代码

  Sub Delete0s（）
 Dim ws As Works heet 
 Dim rng As Range 
 Dim lastRow As Long 
 
设置ws = ThisWorkbook.Sheets（Sheet1）
 lastRow = ws.Range（A& ; ws.Rows.Count）.End（xlUp）.Row 
 
设置rng = ws.Range（A1：A& lastRow）
带有rng 
 .AutoFilter字段：= 1，Criteria1：== 0
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
结束
 
 ws.AutoFilterMode = False 
 End Sub 
  

结果

I have a spreadsheet that populates rows based on data from a pivot table (imported through ODBC). I'm using VLOOKUP, for example:

=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.

Is this achievable somehow? I hope I'm making sense.

Thanks.

解决方案

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

---

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

这篇关于Excel VBA - 扫描范围并删除空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！