CODEWITHSUNDEEP
vbaexcelrows
user1490083
user1490083

Reputation: 391

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

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?

Upvotes: 1

Views: 12732

Answers (3)

cyrus
cyrus

Reputation: 11

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

Upvotes: 1

enderland
enderland

Reputation: 14145

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

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 9

Related Questions