Excel VBA删除行-如何使其更快? [英] Excel VBA to delete rows -- how to make this quicker?

问题描述

我目前正在使用我在stackxchg上找到的以下代码片段删除行，从而在A列中没有数值.这行得通，但是对于具有5000行的工作表来说，速度却非常慢.有什么办法可以使这件事更快地进行吗?概念是，我有一些行仅在满足条件时才会显示日期，并且将使用此列中的日期生成图表.我希望图表范围引用随行而变化，但这很难，因为行中一直向下有公式(要使图表看起来不错，行必须完全为空).我的解决方法是找到一个可以删除这些行的宏(但是使用此代码太慢了).任何帮助，将不胜感激.

I am current using the below code snippet i found on stackxchg to delete rows that whereby there is no numeric value in column A. This works however it is gruesomely slow for a sheet with 5000 rows. Is there any way I can get this thing to go faster? The concept is, I have some rows that will kick out dates only if a criteria is met, and a chart will be generated using the dates in this column. I would like the chart range reference to change with the rows, but this is tough since there are formulas in the rows all the way down (and for a chart to look good the rows need to be completely empty). My workaround was to find a macro which could delete these rows (but it's going too slow using this code). Any help would be appreciated.

Sub Sample()
Dim LR3 As Long, i3 As Long

With Sheets("Basket Performance")
    LR3 = .Range("A" & .Rows.Count).End(xlUp).Row

    For i3 = LR3 To 2 Step -1
        If Not IsNumeric(.Range("A" & i3).Value) Or _
        .Range("A" & i3).Value = "" Then .Rows(i3).Delete
    Next i3
End With

结束子

推荐答案

您可以在循环结束时进行一次删除:

You can do a single delete at the end of your loop:

Sub Sample()

    Dim LR3 As Long, i3 As Long, rng As Range

    With Sheets("Basket Performance")
        LR3 = .Range("A" & .Rows.Count).End(xlUp).Row

        For i3 = LR3 To 2 Step -1
            If Not IsNumeric(.Range("A" & i3).Value) Or _
                           .Range("A" & i3).Value = "" Then 
                If rng Is Nothing Then
                    Set rng = .Cells(i3, 1)
                Else
                    Set rng = application.union(rng, .Cells(i3, 1))
                End If
            End If '<<EDIT
        Next i3
    End With

    If Not rng Is Nothing then rng.Entirerow.Delete

End Sub

这篇关于Excel VBA删除行-如何使其更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！