如何在包含数据的范围内删除每个行

Question

在大数据集中，我试图在包含任何数据的列中找到每个单元格中的每个单元格，因此"不是空白"，然后删除包含数据的行。但是，我的代码无法使用：

Dim rng3 As Range
Do
    Set rng3 = Columns("J:J")
    If Not rng3 Is Nothing Then
        rng.EntireRow.Delete
    Else
        Exit Do
    End If
Loop

我希望有人可以帮助我。

Answer 1

1. 选择您需要的范围
2. 按F5
3. 按Special...按钮
4. 选择Constants或Formulas（如果需要的话，请使用其他选择）
5. 按OK
6. 现在转到：Home-> Cells-> Delete（箭头） -> Delete Sheet Rows

update

宏：

Sub DeleteRowsWithData()
    Dim rng As Range
    ' Change to the range you need
    Set rng = Range("A1").CurrentRegion
    ' Change field to the one you need
    rng.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlFilterValues
    With rng
        With .Offset(1).Resize(.Rows.Count - 1) 'Exclude header
            Application.DisplayAlerts = False
            .Rows.Delete
            Application.DisplayAlerts = True
        End With
    End With
End Sub

Answer 2

几个时刻：

您测试Range不为Nothing-它将始终返回True，因为它实际上是指对象。用于测试单元格的内容，使用IsEmpty()。

。

如果要删除行，请运行循环向后。

Sub Test()
    Dim Rng As Range
    Dim I As Integer
'You need only non-empty cells, thus the intersection of needed column and used range of your worksheet
    Set Rng = Intersect(ActiveSheet.UsedRange, Columns("J"))
'Loop the cells backwards, from the last row to the first
    For I = Rng.Rows.Count To 1 Step -1
'If the cell is not empty, the delete that row and continue upwards
        If Not IsEmpty(Rng.Cells(I)) Then
            Rng.Cells(I).EntireRow.Delete
        End If
    Next
End Sub

Answer 3

这个简单的代码将有能力：

For i = Cells(Rows.Count, 10).End(xlUp).Row To 1 Step -1
    If Cells(i, 10).Value <> "" Then
        Cells(i, 10).EntireRow.Delete
    End If
Next i