gpt4 book ai didi

excel - 删除行的过程缓慢 - 如何加快速度?

转载 作者:行者123 更新时间:2023-12-02 16:08:51 26 4
gpt4 key购买 nike

我的工作簿中有几个宏。这是唯一一个在 2500 行表上慢 3-5 分钟的唯一一个。

目的是如果行位于日期 dtFrom 和 dtUpTo 之间,则删除整行。

我添加了暂停和恢复计算,这稍微提高了它

有人对如何加快速度有任何想法吗?

Sub DeleteRows
'--- Pause Calculations:
Application.Calculation = xlManual
'----- DELETE ROWS -----
Dim dtFrom As Date
Dim dtUpto As Date
Dim y As Long
Dim vCont As Variant
dtFrom = Sheets("Control Panel").Range("D5").Value
dtUpto = dtFrom + 6
Sheet1.Range("D1").Value2 = "Scanning, Please wait..."
With Sheets("Database")
For y = Sheet5.Cells(Sheet5.Rows.Count, 2).End(xlUp).Row + 1 To 2 Step -1
vCont = .Cells(y, 1).Value
If Not IsError(vCont) Then
If vCont >= dtFrom And vCont <= dtUpto Then
.Rows(y).EntireRow.Delete
End If
End If
Next
End With
'--- Resume Calculations:
Application.Calculation = xlAutomatic
End Sub

谢谢!

最佳答案

尝试仅对末尾的所有相关行执行一次删除操作:

Sub DeleteRows()
'--- Pause Calculations:
Application.Calculation = xlManual
'----- DELETE ROWS -----
Dim dtFrom As Date
Dim dtUpto As Date
Dim y As Long
Dim vCont As Variant
Dim rDelete As Range
dtFrom = Sheets("Control Panel").Range("D5").Value
dtUpto = dtFrom + 6
Sheet1.Range("D1").Value2 = "Scanning, Please wait..."
With Sheets("Database")
For y = Sheet5.Cells(Sheet5.Rows.Count, 2).End(xlUp).Row + 1 To 2 Step -1
vCont = .Cells(y, 1).Value
If Not IsError(vCont) Then
If vCont >= dtFrom And vCont <= dtUpto Then
If rDelete Is Nothing Then
Set rDelete = .Rows(y)
Else
Set rDelete = Union(rDelete, .Rows(y))
End If
End If
End If
Next
End With
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
'--- Resume Calculations:
Application.Calculation = xlAutomatic
End Sub

注意:您还可以在此处使用自动过滤器。

关于excel - 删除行的过程缓慢 - 如何加快速度?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35605424/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com