vba 基于列中的非空单元格删除行的VBA代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26609484/
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
VBA code to delete a row based on a non empty cell in a column
提问by user2980029
I am running a report of employees who charge time to different codes. The report gives me the following columns:
我正在运行一份关于按不同代码收取时间的员工的报告。该报告为我提供了以下列：
Emp# / Emp Name / Rate / TermDate
Emp# / Emp Name / Rate / TermDate
If the employee has left, then there is a value in the TermDate column.
如果员工已离职，则 TermDate 列中有一个值。
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.
每个报告也将有不同的行数，因此我需要帮助选择范围并根据最后一列的值删除范围中的行。
回答by Siddharth Rout
Fastest way. Use .Autofilter. No need to loop...
最快的方式。使用.Autofilter. 不需要循环...
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
回答by Gary's Student
This assumes that the column to check is column D
这假设要检查的列是列D
Sub RowKiller()
Dim col As String, N As Long, i As Long
col = "D"
N = Cells(Rows.Count, col).End(xlUp).Row
For i = N To 1 Step -1
If Cells(i, col).Value <> "" Then
Cells(i, col).EntireRow.Delete
End If
Next i
End Sub
回答by Gary_W
Consider using a filter on the data instead. You didn't mention what version of Excel, but in 2010 go to the data tab, select the termdate column, then click on "filter".
考虑对数据使用过滤器。你没有提到什么版本的Excel，但在2010年去数据选项卡，选择termdate列，然后点击“过滤器”。
Notice a little dropdown arrow appears in the first cell of the column. Click on that, then deselect all but the "blanks" selection. Wha-la.
请注意，列的第一个单元格中会出现一个小下拉箭头。单击它，然后取消选择除“空白”选择之外的所有选项。哇啦。
That way the data is still there for history if you need it.
这样，如果您需要，历史数据仍然存在。
Oh and after applying the filter, a little funnel icon will remain in the first cell of the column to show it's a filtered column.
哦，应用过滤器后，一个小漏斗图标将保留在列的第一个单元格中，以显示它是一个过滤列。
回答by marcel89
My solution would be:
我的解决方案是：
Dim lng_LastRow As Long
Dim lng_i As Long
lng_LastRow = Range("A1").End(xlDown).Row
For lng_i = 1 To lng_LastRow
If Len(Trim(Cells(lng_i, 4).Value)) > 0 Then
Rows(lng_i).Delete
lng_i = lng_i - 1
End If
Next lng_i
回答by mr.bjerre
If I understand correctly, then you want something like this?
如果我理解正确，那么您想要这样的东西吗？
Sub DeleteNonEmptyRows()
Dim row As Integer, N As Integer
N = Cells(Rows.Count, "D").End(xlUp).Row
For row = N To 2 Step -1
If Not IsEmpty(Range("D" & row)) Range("A" & row).EntireRow.Delete
Next row
End Sub
It is assumed that TermDate is in column D.
假设 TermDate 在 D 列中。
