Delete multiple rows in excel using VBA based on an array of numbers

Question

I populate an array of numbers with some criteria and then what I am trying to get to is deleted all of the rows that are in this area.

Basically I go through a column and if in that specific row, the cell in this column matches a criteria, I add that row number into an array. After it is done going through all rows I want to delete all of the row numbers.

I'm having trouble figuring out how to delete all rows at once because obviously if I do it one at a time the row numbers change as the one prior or below gets deleted. Because of this I want to select all of the rows together and then just call the Delete command on all rows at once. ANy ideas?

You could just store a value in a hidden column as you go through the rows, indicating that row needed to be deleted. — Taylor K.
– Taylor K., Commented Oct 3, 2012 at 20:29
Use autofilter to filter the rows based on your criteria, then delete the hidden rows in one hit using specialcells. — Reafidy
– Reafidy, Commented Oct 3, 2012 at 21:47

Tim Williams · Accepted Answer · 2012-10-03 20:45:13Z

9

Sub Tester()
Dim arr
    arr = Array(3, 5, 7, 9)
    ActiveSheet.Range("A" & Join(arr, ",A")).EntireRow.Delete
End Sub

answered Oct 3, 2012 at 20:45

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

enderland · Accepted Answer · 2012-10-03 20:45:56Z

2

Iterate backwards through your rows.

Something like:

Sub tester()

    'setting ScreenUpdating false makes this go faster...
    Application.ScreenUpdating = False
    Dim i As Integer
    'go through all rows starting at last row
    For i = Range("A1:E5").Rows.Count To 1 Step -1
        'check if you need to delete them (you will want to update this)
        If Cells(i, 1).Value = "Delete this row!" Then
            Rows(i).Delete
        End If

   Next i


 Application.ScreenUpdating = True
End Sub

answered Oct 3, 2012 at 20:45

enderland

14.2k18 gold badges105 silver badges157 bronze badges

1 Comment

Tim Stack Over a year ago

This does not delete all rows in the array at once (as asked in the question) but relies on a loop and is thus a lot slower than Tim William's solution

Bob Gilmore · Accepted Answer · 2014-03-05 14:16:25Z

1

Here's a simple one:

If Range("B1") <> "" Then ' Range that bears the array of cell.addresses....

    ar = Array(Range(Range("B1").Cells))

    For Each a In ar
        a.EntireRow.ClearContents
    Next

    Range("B1").ClearContents
End If

edited Mar 5, 2014 at 14:16

Bob Gilmore

14k13 gold badges49 silver badges55 bronze badges

answered Mar 5, 2014 at 13:52

cyrus

111 bronze badge

Collectives™ on Stack Overflow

Delete multiple rows in excel using VBA based on an array of numbers

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related