Excel VBA to delete rows — how to make this quicke

问题:

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

End Sub

回答1:

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

回答2:

you can try this

Option Explicit

Sub delrow()

With ThisWorkbook.Worksheets("Basket Performance")
    .Columns("A").Insert '<== insert a "helper" column for counting and sorting purposes. it'll be removed by the end of the macro
    .Columns("B").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, -1).FormulaR1C1 = "=COUNT(R1C[1]:RC[1])"
    .Cells.Sort key1:=.Columns("A"), order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlNo
    .Columns("A").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete '<== maybe you don't need to delete but just define the chart range reference from row 1 down to the last row in column A with a number
    .Columns("A").Delete '<== remove the "helper" column
End With

End Sub

you may want to consider not deleting "non numeric" rows once sorted out, and just defining the chart range reference from row 1 down to the last row in column A with a number instead