时间戳

首页 > 解决方案 > VBA - 在某些列为空之后删除某些行

excel - VBA - 在某些列为空之后删除某些行

问题描述

我有一个 excel 表，如下所示：

Name  task date1  date2 date3  date4
John  t1    d1
Lucy  t2    
Mary  t3    d2    d3

我想删除第三列之后的列（即 from date1）为空的行（例如本例中的 Lucy）

我找到了下面的示例，我认为它很接近，但不确定如何通过选择第三列之后的空行而不是整行来修改它。

Sub DeleteAllEmptyRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range

    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False

    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then 'Some logic to add here to choose range
            Rows(RowIndex).Delete
        End If
    Next RowIndex

    Application.ScreenUpdating = True
End Sub

请问有什么建议吗？谢谢。

标签： excelvba

解决方案

我认为您需要的只是首先计算使用范围中的最后一列，然后在循环内的 CountA 语句中使用它。

要计算最后一列，您可以在循环之前添加以下行：

Dim LastColIndex As Integer: LastColIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

然后，该If语句需要看起来像这样：

If WorksheetFunction.CountA(Range(Cells(RowIndex, 3), Cells(RowIndex, LastColIndex))) = 0 Then

这将检查从当前行的第 3 列开始的非空值。

合并后的代码将如下所示：

Sub DeleteAllEmptyRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range

    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False

    Dim LastColIndex As Integer: LastColIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

    For RowIndex = LastRowIndex To 1 Step -1
        If WorksheetFunction.CountA(Range(Cells(RowIndex, 3), Cells(RowIndex, LastColIndex))) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex

    Application.ScreenUpdating = True
End Sub

我还将For循环更改为倒数到 2 而不是 1 以避免删除标题行的可能性，但我想这取决于你的口味:)

在幻像值的情况下...

有时，单元格的值是肉眼不可见的，但由 Excel 的COUNTA函数计算，例如当一个单元格有一个或多个空格作为其值时。如果需要考虑这些情况，我们可以使用以下 VBA 函数单独检查每个单元格：

Function IsBlankRange(ByRef rng As Range) As Boolean
    'For faster processing, read in the entire range into a memory variable
    '  instead of reading in each cell value from Excel
    Dim arr As Variant: arr = rng.Value

    'If the range contains a single value, check it and exit
    If Not IsArray(arr) Then 'With single-value ranges, arr will not be an array
        IsBlankRange = Trim(arr & "") = ""
        Exit Function
    End If

    'If we are here, the range contains an array of values,
    '  so we must loop through the array

    'Assume the range is not blank
    IsBlankRange = False

    Dim arrLb1 As Long: arrLb1 = LBound(arr, 1)
    Dim arrUb1 As Long: arrUb1 = UBound(arr, 1)
    Dim arrLb2 As Long: arrLb2 = LBound(arr, 2)
    Dim arrUb2 As Long: arrUb2 = UBound(arr, 2)
    Dim i As Long, j As Long
    For i = arrLb1 To arrUb1
        For j = arrLb2 To arrUb2
            'Return false (the value assumed above) on the first non-blank value
            If Trim(arr(i, j) & "") <> "" Then Exit Function
        Next
    Next

    'If we are here, no non-blank value was encountered, so the range is blank
    IsBlankRange = True
End Function

此功能可用于将If WorksheetFunction.CountA(...) = 0线路更改为

        If IsBlankRange(Range(Cells(RowIndex, 3), Cells(RowIndex, LastColIndex))) Then

简而言之，新程序如下所示：

Sub DeleteAllEmptyRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range

    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False

    Dim LastColIndex As Integer: LastColIndex = UsedRng.Column - 1 + UsedRng.Columns.Count

    For RowIndex = LastRowIndex To 1 Step -1
        If IsBlankRange(Range(Cells(RowIndex, 3), Cells(RowIndex, LastColIndex))) Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex

    Application.ScreenUpdating = True
End Sub

当然，IsBlankRange也需要添加新功能（DeleteAllEmptyRows可能在代码下方）。如果该函数永远不会在其他任何地方使用，则可以将该函数设为模块私有。

但是，如果你可以使用 CONCAT ...

IsBlankRange如果您有 Excel 2019 并且可以使用新功能，则可以简化该功能CONCAT，该功能可以应用于范围：

Function IsBlankRange(ByRef rng As Range) As Boolean
    IsBlankRange = Trim(WorksheetFunction.Concat(rng) & "") = ""
End Function

{CONCAT函数在https://www.excelfunctions.net/excel-concat-function.html有解释}

除了使用IsBlankRange函数，CONCAT也可以直接在循环If内的语句中使用：For

        If Trim(WorksheetFunction.Concat(Range(Cells(RowIndex, 3), Cells(RowIndex, LastColIndex))) & "") = "" Then

但是，我相信将范围评估留在单独的函数中可以更容易地根据所使用的 Excel 版本进行自定义，并且使If语句更易于阅读。

推荐阅读