Excel VBA Store row numbers in Array and delete multiple rows at once

Question

I'm trying to delete all rows on my worksheet that have a unique value in column B.

I know this can be done with a filter or conditioned formatting, but I would like to know if the following is possible as well, since it could be useful in other situations:

I want to loop through all rows and store the row number in an Array if the row has a unique value in column B. Then delete all the rows whose number is stored in the Array in one single action.

The reasoning for storing the row numbers in an Array instead of deleting the desired rows in the loop is to reduce runtime.

My data varies in number of rows but is always in column A:K and it always begins on row 6.

Below is the code I've written with inspiration from the following links:
Dynamically adding values to the array on the go.
Deleting rows whose number is stored in array in one single action (see Tim Williams answer).

I get the error message: Run-time error '5': Invalid procedure call or Argument

Sub DeleteRows()
Dim ws4 As Worksheet: Set ws4 = Worksheets("Sheet1")
Dim LastRow As Long
Dim CurrentRow As Long
Dim GroupValue
Dim GroupTotal As Long
Dim MyArray()
Dim y As Long

Application.ScreenUpdating = False
ws4.Activate

GroupValue = ws4.Range("B6").Value ' Sets the first GroupValue
CurrentRow = 6 ' Sets the starting row
y = 0
LastRow = ws4.Cells(Rows.Count, "B").End(xlUp).Row

    For x = 1 To LastRow

        GroupTotal=Application.WorksheetFunction.CountIf(Range("B6:B"&LastRow), _
            GroupValue) ' Searches for the GroupValue and finds number of matches
        If GroupTotal = 1 Then ' If GroupTotal = 1 then add the row# to the array
            ReDim Preserve MyArray(y)
            MyArray(y) = CurrentRow
            y = y + 1
        End If

        CurrentRow = CurrentRow + GroupTotal 'set the next row to work with
        GroupValue = Range("B" & CurrentRow).Value 'set next GroupValue to find

        If GroupValue = "" Then ' Checks to see if the loop can stop
            Exit For
        End If

    Next x

'***This should delete all the desired rows but instead produces the error.***
ws4.Range("B" & Join(MyArray, ",B")).EntireRow.Delete
Application.ScreenUpdating = True

End Sub

I've researched for hours and tried to manipulate the code with no luck.

@ashleedawg 4th to last line in the code: ws4.Range("B" & Join(MyArray, ",B")).EntireRow.Delete — DirtyDeffy
– DirtyDeffy, Commented May 31, 2018 at 13:32
This doesn[t make a lot of sense - you're saying you know of easier ways to do this but instead you've decided to spent "hours" working on a more complicated method, because it could be useful in other situations? Anyway, the first logical troubleshooting step is, find out what the values are of your variables/expressions on the line that causes the error. (Stop just before the error by inserting a line before it with the Stop command. — ashleedawg
– ashleedawg, Commented May 31, 2018 at 13:39
Ok, wasn't expecting that to work: MyArray = Array(2, 4, 5, 8, 10, 15):ActiveSheet.Range("B" & Join(MyArray, ",B")).EntireRow.Delete but it did.... now I want to know why it didn't work for you. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented May 31, 2018 at 13:41
A couple of things first off - is Sheet1 active when you run the code? Your CountIf looks at just Range("B6:B"... and doesn't take the sheet into account. You also have If GroupValue = "" while GroupValue is a Long, so shouldn't it be If GroupValue = 0? Also the first element of MyArray is Empty and the second holds the value - the array will be 0 based so let Y=0 at the start. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented May 31, 2018 at 13:52

Darren Bartrup-Cook · Accepted Answer · 2018-06-04 19:50:38Z

2

Use a variable defined as a Range and Union each row to it.
In the example below MyArray is the array of row numbers that should be deleted.

Public Sub Test()

    Dim MyArray() As Variant

    MyArray = Array(2, 4, 5, 8, 10, 15)

    DeleteRows MyArray

End Sub

Public Sub DeleteRows(RowNumbers As Variant, Optional SheetName As String = "")

    Dim wrkSht As Worksheet
    Dim rRange As Range
    Dim x As Long

    On Error GoTo ERROR_HANDLER

    If SheetName = "" Then
        Set wrkSht = ActiveSheet
    Else
        Set wrkSht = ThisWorkbook.Worksheets(SheetName)
    End If

    For x = LBound(RowNumbers) To UBound(RowNumbers)
        If rRange Is Nothing Then
            Set rRange = wrkSht.Rows(RowNumbers(x))
        Else
            Set rRange = Union(rRange, wrkSht.Rows(RowNumbers(x)))
        End If
    Next x

    If Not rRange Is Nothing Then rRange.Delete

    On Error GoTo 0

Exit Sub

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure DeleteColumns."
            Err.Clear
            Application.EnableEvents = True
    End Select

End Sub

Edit
The Test procedure can be replaced with any code that creates an array of row numbers. The array is then passed to the DeleteRows procedure. You could also pass it a sheet name to delete the rows from: DeleteRows MyArray, "Sheet2".

The DeleteRows procedure sets up the variables, turns error checking on and then checks if a sheet name was passed to it. It then sets a reference to either the active sheet or the named sheet. You could also check if the passed sheet actually exists here.

Next a loop starts going from the first to last element of the array. The first is usually 0 so you could replace LBOUND(RowNumbers) with 0.

rRange is the variable that's going to hold the row references to delete and Union won't work if it doesn't already hold a range reference.
On the first pass of the loop it won't hold a reference so will be nothing and the first row in the array will be set as the first row reference on the sheet held in wrkSht.
On subsequent passes rRange will already hold a reference so the next row will be unioned to it.
Those two decisions are made in an IF...END IF block seperated by an ELSE statement.

After the loop has finished a single line IF statement - no END IF required on single line - checks if rRange holds any references. If it does then those rows are deleted.

The procedure exits the main body of code, deals with the error handling and then ends.

edited Jun 4, 2018 at 19:50

answered May 31, 2018 at 13:36

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

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

3 Comments

DirtyDeffy Over a year ago

Thanks a lot for taking the time to answer! However, there are a lot of things that I don't understand in your code as I'm still pretty new to VBA. First off - what does your ERROR_HANDLER do? Regarding the bulk of the code, it would be awesome if you could explain a bit of the logic behind it? Thank you in advance :)

Darren Bartrup-Cook Over a year ago

Only just seen this comment. Without an error handler code will just fall over whenever an error occurs. The code I've put in there doesn't do anything at the moment - just displays whatever message an error would give, but you could deal with any expected errors - if, for example, a non-existent sheet name was passed to the procedure you'd get a Subscript out of range error. You could add Case 9: Set wrkSht = ActiveSheet:Resume Next which will default to the Activesheet in the case of that error occuring.

Darren Bartrup-Cook Over a year ago

Have updated my answer with an explanation of the code.

Collectives™ on Stack Overflow

Excel VBA Store row numbers in Array and delete multiple rows at once

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related