Excel VBA to delete rows -- how to make this quicker?

Question

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

Also, I would like to create another macro that would extend the row back down to 5000 rows. If it's easier, maybe just a macro which will change the chart reference range depending on how many rows down have numeric values! (thatd be preferable). Thanks for any help — Jemie, Mar 17 '16 at 17:31
Turn off the calculations and disable events from firing, most likely this is what is slowing it down. Beyond that you can look into creating a union range and deleting the whole at once. — Scott Craner, Mar 17 '16 at 17:32
See [Slow process on deleting rows - How to make faster?](http://stackoverflow.com/questions/35605424/slow-process-on-deleting-rows-how-to-make-faster/35606697#35606697). — , Mar 17 '16 at 17:51
Sorry I'm a newb -- how can I do either of these things? The other answers' havent worked — Jemie, Mar 17 '16 at 19:56

Tim Williams · Answer 1 · 2016-03-17T20:55:01.153

2

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

edited Mar 17 '16 at 20:55

answered Mar 17 '16 at 17:41

Tim Williams

154,628
8
97
125

I'm getting a "Next without for" error on that last Next – Jemie Mar 17 '16 at 19:54
Missed the End If - added above – Tim Williams Mar 17 '16 at 20:55

score 0 · Answer 2 · answered Mar 18 '16 at 06:58

0

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

answered Mar 18 '16 at 06:58

user3598756

28,893
4
18
28

Hi user -- your solution would be very ideal -- as it seems all the VBA codes posted here so far do not recognize formulas with values as Numeric (they're deleting all the rows with formulas that are actually kicking out values). If you could help me figure out how to define a chart range in VBA to do this, it would be highly appreciated. – Jemie Mar 21 '16 at 13:22
if I answered your question, please accept my solution. as for the chart range thing you may have the macro recorder work for you and then look at the auto generated code. should you still have problems you could start a new question. in this latter case I'd recommend you to act as you did in this case, i.e. showing your code and all your efforts made before asking for help and detailing what went wrong with them – user3598756 Mar 21 '16 at 13:46
It should be noted that delete sorted range is MUCH faster. Could be thousand time faster. Excel ALWAYS try to delete the *bottom* rows first, so there is as little row shifting as possible. Try to get deleted row next to each other, that cut 99.9% of processing time .Try to put the rows to delete at the bottom where possible, it would save 30% time on top of that. – Tam Le Jun 09 '20 at 04:31

Excel VBA to delete rows -- how to make this quicker?

2 Answers2