VBA代码可基于列中的非空单元格删除行 [英] VBA code to delete a row based on a non empty cell in a column

问题描述

我正在运行一份报告，报告按时间收取不同代码的员工。该报告为我提供了以下几列：

I am running a report of employees who charge time to different codes. The report gives me the following columns:

Emp＃/ Emp名称/费率/ TermDate

Emp# / Emp Name / Rate / TermDate

如果员工已经走了，那么TermDate列中就有一个值。

If the employee has left, then there is a value in the TermDate column.

由于单元格中的值可以是任何日期，因此我想编写一个宏来搜索列表并删除第四列中的值不为空的任何行。

Because the value in the cell could be any date, I want to write a macro that will search the list and delete any row in which the value in the fourth column is NOT blank.

我发现了几个示例，这些示例基于空白单元格或基于

I've found several examples of how to delete a row based on blank cells, or based on certain values, but none on a non-blank value.

每个报表的行数也不同，因此我需要选择范围并删除其中的行范围取决于最后一列的值。

Each report will have a different number of rows as well so I need help selecting the range and deleting rows in the range based on the value of the final column.

推荐答案

最快的方法。使用。自动过滤器。无需循环...

Fastest way. Use .Autofilter. No need to loop...

Sub Sample()
    Dim LRow As Long
    Dim delRange As Range

    With ThisWorkbook.Sheets("Sheet1")
        '~~> Remove any filters
        .AutoFilterMode = False

        LRow = .Range("D" & .Rows.Count).End(xlUp).Row

        With .Range("A1:D" & LRow)
            .AutoFilter Field:=4, Criteria1:="<>"
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    If Not delRange Is Nothing Then delRange.Delete
End Sub

这篇关于VBA代码可基于列中的非空单元格删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！