vba 宏 - 根据日期删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25033022/
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
Macro - delete rows based on date
提问by user3606198
I am very new to VBA and macros in Excel. I have a very large excel spreadsheet in which column A holds dates. I am trying to delete the rows which have a value smaller than a certain date and this is what I have come up with till now..
我对 Excel 中的 VBA 和宏非常陌生。我有一个非常大的 Excel 电子表格，其中 A 列包含日期。我正在尝试删除值小于某个日期的行，这就是我到现在为止想出的..
Sub DELETEDATE()
Dim x As Long
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(x, "A").Value
If CDate(Cells(x, "A")) < CDate("01/01/2013") Then
Cells(i, "A").EntireRow.Delete
End If
Next x
Next i
End Sub
I am receiving a Next without For error... can somebody help please?
我收到 Next 没有错误...有人可以帮忙吗？
回答by Dan Wagner
This lends itself well to using the .AutoFilterproperty of a Range. The script below contains a comment for each step taken:
这非常适合使用 a 的.AutoFilter属性Range。下面的脚本包含对所采取的每个步骤的注释：
Option Explicit
Sub DeleteDateWithAutoFilter()
Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long
'turn off alerts
Application.DisplayAlerts = False
'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
'identify the last row in column A and the last col in row 1
'then assign a range to contain the full data "block"
With MySheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
'apply autofilter to the range showing only dates
'older than january 1st, 2013, then deleting
'all the visible rows except the header
With MyRange
.AutoFilter Field:=1, Criteria1:="<1/1/2013"
.SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With
'turn off autofilter safely
With MySheet
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With
'turn alerts back on
Application.DisplayAlerts = True
End Sub
Running this code on a simple example (on "Sheet1" in this picture) that looks like this:
在一个简单的示例（在这张图片中的“Sheet1”上）上运行此代码，如下所示：
Will delete all rows with a date older than 1/1/2013, giving you this result:
将删除日期早于 1/1/2013 的所有行，结果如下：
回答by Mark Fitzgerald
To answer your question
回答你的问题
I am receiving a Next without For error
我收到 Next 没有 For 错误
The problem is you are trying to loop on ibut you haven't opened a For iloop. When you indent the code below any code that invokes a Loopor condition (i.e. If) it becomes obvious
问题是您正在尝试循环，i但您还没有打开For i循环。当您在调用 aLoop或条件（即If）的任何代码下方缩进代码时，它变得显而易见
Sub DELETEDATE()
Dim x As Long
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(x, "A").Value
If CDate(Cells(x, "A")) < CDate("01/01/2013") Then
Cells(i, "A").EntireRow.Delete 'i has no value so Cells(0, "A") is ??
End If
Next x
Next i 'where is the For i = ... in this code?
End Sub
When writing code I try to:
在编写代码时，我尝试：
- Enter the end command immediately if it's needed. So type
If...Then, hit[ENTER], typeEnd If, hit[HOME], hit[ENTER], hit[UP ARROW]then[TAB]to the right place to write the conditional code so that anyone will be able to read and understand it easily. - Always use
Option Explicitat the top of every module to force variable declarations.
- 如果需要，请立即输入结束命令。因此，键入
If...Then, hit[ENTER], typeEnd If, hit[HOME], hit[ENTER], hit[UP ARROW]然后[TAB]到正确的位置编写条件代码，以便任何人都能够轻松阅读和理解它。 - 始终
Option Explicit在每个模块的顶部使用以强制变量声明。
a tip about deleting rows based on a conditionIf you start at the top and work down, every time you delete a row your counter will effectively move to the cell two rows below the row you deleted because the row immediately below the deleted row moves up (i.e. it is not tested at all).
关于根据条件删除行的提示如果您从顶部开始向下工作，则每次删除一行时，您的计数器都会有效地移动到您删除的行下方两行的单元格，因为紧接删除行下方的行向上移动（即它根本没有经过测试）。
The most efficient way is to loop up from the bottom or your rows:
最有效的方法是从底部或您的行向上循环：
Sub DELETEDATE()
Dim x As Long
For x = [a1].SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
Debug.Print Cells(x, "A").Value
If CDate(Cells(x, "A")) < CDate("01/01/2013") Then
Cells(x, "A").EntireRow.Delete 'changed i to x
End If
Next x
End Sub
This way, the next row you want to test has been preserved - you've only moved the row below up by 1 and you've tested that row earlier.
这样，您要测试的下一行就被保留了 - 您只将下面的行向上移动了 1，并且您之前已经测试了该行。
回答by Karthick Gunasekaran
Please try with this
请试试这个
Sub DELETEDATE()
Dim x As Long
last = Range("A65536").End(xlUp).Row
For x = 1 To last
Debug.Print Cells(x, "A").Value
check:
If x <= last Then
If Trim(CDate(Cells(x, "A"))) <= Trim(CDate("7/29/2013")) Then
last = last - 1
Cells(x, "A").EntireRow.Delete
GoTo check
End If
End If
Next x
End Sub
回答by Gareth
You have an additional Next ifor some reason in your code as highlighted by the debugger. Try the below:
Next i由于调试器突出显示，您的代码中出于某种原因有一个额外的。试试下面的：
Sub DELETEDATE()
Dim x As Long
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(x, "A").Value
If CDate(Cells(x, "A")) < CDate("01/01/2013") Then
Cells(i, "A").EntireRow.Delete
End If
Next x
End Sub
