vba 从某个单元格中删除Excel VBA中的整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17146476/
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
Deleting Entire Row in Excel VBA From A Certain Cell Down
提问by clattenburg cake
I am trying to delete whole rows of data if a date is more than a week later than today, but I don't want to delete the header. Today's date is a variable appearing in A2.
如果日期比今天晚一个多星期，我会尝试删除整行数据，但我不想删除标题。今天的日期是出现在 A2 中的一个变量。
Edit: Column A has dates in the format of dd/mm/yyyy.
编辑：A 列的日期格式为 dd/mm/yyyy。
This code is what I have at the moment but it doesn't work and erases the header:
这段代码是我目前所拥有的，但它不起作用并擦除了标题：
Sub deletedates()
Dim firstDate As Date, secondDate As Date
Dim i As Range
firstDate = DateValue(Range("A2"))
secondDate = DateAdd("d", 6, firstDate)
MsgBox secondDate
For Each i In Range("A:A")
If i.Value > secondDate Then
i.Select
ActiveCell.EntireRow.Delete
End If
Next i
End Sub
回答by Skip Intro
I've added something that helps two issues:
我添加了一些有助于解决两个问题的内容：
1) The lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row finds the last row of data, as your original code will look at each cell in Column A, which could be over a million if you use newer versions of Excel.
1) lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row 查找最后一行数据，因为您的原始代码将查看 A 列中的每个单元格，这可能超过百万，如果您使用较新版本的 Excel。
2) I have started the search for data at row 2 of column A to avoid deleting the header row.
2) 我已经开始在 A 列的第 2 行搜索数据以避免删除标题行。
Hope this helps!
希望这可以帮助！
Sub deletedates()
Dim firstDate As Date, secondDate As Date
Dim i As range
Dim lngCounter As Long
Dim strRange As String
lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row 'Find last row of data
strRange = "a2:a" & lngCounter 'Start at Row 2, Column A, finish at last row of Column A
firstDate = DateValue(range("A2"))
secondDate = DateAdd("d", 6, firstDate)
'MsgBox secondDate
For Each i In range(strRange) 'Starts at second row of data.
If i.Value > secondDate Then
i.Select
ActiveCell.EntireRow.Delete
End If
Next i
End Sub
