Selecting a range of cells throws run-time error '1004'

Question

I'm writing a VBA problem which finds lines which are considered to be invalid, saves the row number in an array called invalidRowsToDelete and deletes the selected rows by building a string of the invalid rows (e.g. 1:1, 4:4, 7:7).

However, it sometimes works, it sometimes doesn't, but fails more consistently with large amounts of invalid rows.

The error it returns is: "Runtime error '1004': Method range of object '_global' failed" on the line Set rng = Range(invalidRowsToDelete)

Public rng As Range
__________________________


Dim invalidRowsToDelete As String
Dim i As Long

For i = LBound(InvalidFilesArr) To UBound(InvalidFilesArr)

    If InvalidFilesArr(i) <> "" Then
        invalidRowsToDelete = invalidRowsToDelete & InvalidFilesArr(i) & ":" & InvalidFilesArr(i) &     ","
    Else
        Exit For
    End If
Next i

'Build range statement and delete trailing comma from rowsToDeleteStatement
invalidRowsToDelete = "" & Left(invalidRowsToDelete, Len(invalidRowsToDelete) - 1) & ""
Debug.Print invalidRowsToDelete

Worksheets("Sheet1").Activate

Set rng = Range(invalidRowsToDelete) #### Problem line

rng.Select
rng.Delete

This has had me for quite a while now and I can't work out the reason for it causing this error.

Thanks

Answer 1

Using a string to build a complicated range reference is generally a bad idea. I would use the loop to build the range instead of the string that describes the range.

Public rng As Range
__________________________


Dim invalidRowsToDelete As String
Dim i As Long

i = LBound(InvalidFilesArr)
Set rng = Worksheets("Sheet1").Rows(InvalidFilesArr(i))

For i = LBound(InvalidFilesArr) + 1 To UBound(InvalidFilesArr)
    If InvalidFilesArr(i) = "" Then
        Exit For
    End If
    Set rng = Union(rng, Worksheets("Sheet1").Rows(InvalidFilesArr(i)))
Next i

rng.Delete

Answer 2

Why not just delete the rows there and then when you find them?

Public rng As Range

Dim invalidRowsToDelete As String
Dim i As Long

Worksheets("Sheet1").Activate
For i = LBound(InvalidFilesArr) To UBound(InvalidFilesArr)
    If InvalidFilesArr(i) <> "" Then
        Set rng = Range(InvalidFilesArr(i) & ":" & InvalidFilesArr(i))
        rng.Select
        rng.Delete
    Else
        Exit For
    End If
Next i  

Answer 3

Your string is "bad"; to find out why replace:

Set rng = Range(invalidRowsToDelete)

with:

On Error GoTo GhostBusters
Set Rng = Range(invalidRowsToDelete)

and at the bottom of the sub include:

GhostBusters:
MsgBox Len(invalidRowsToDelete)
MsgBox invalidRowsToDelete
On Error GoTo 0

Answer 4

Use Union to get the range to be deleted and then call Delete on this range. HTH

Dim rngToDelete As Range
Dim rngRow As Range
Dim rowsToDelete(0 To 5) As Long
Dim i As Long

rowsToDelete(0) = 2
rowsToDelete(1) = 5
rowsToDelete(2) = 7
rowsToDelete(3) = 9
rowsToDelete(4) = 10
rowsToDelete(5) = 12

For i = LBound(rowsToDelete) To UBound(rowsToDelete)
    Set rngRow = ActiveSheet.Rows(rowsToDelete(i))
    If rngToDelete Is Nothing Then
        Set rngToDelete = rngRow
    Else
        Set rngToDelete = Application.Union(rngToDelete, rngRow)
    End If
Next i

if Not rngToDelete Is Nothing Then rngToDelete.Delete