Excel VBA Method 'Range' of object'_global' failed error 1004

Question

I can't for the life of my figure out why I'm getting "'Range' of object'_global' failed" on my do while statement. It's like the range named RC3 isn't being recognized. Any help is greatly appreciated.

Sub DeleteBlankRows()

Dim Allrws As Range
Dim Rws As Range
Dim RC2 As Range
Dim RC3 As Range
Dim I As Integer
Dim CopyRange As Range
Dim LastRow As Long

With Application

'.ScreenUpdating = False ' don't spam the interface with selections

    With ActiveSheet

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' Put the number of the last row in LastRow

    End With

    Set Allrws = Range("A2:S" & CStr(LastRow))  ' set range to be observed

    For Each Rws In Allrws.Rows   ' for each row in the range of Allrws call it Rws and do the following with it

        Set RC2 = Rws.Offset(0, 3).Resize(1, 1)      ' move and resize the range of RC2 to include only the cell under column D
        Set RC3 = Rws.Offset(0, 7).Resize(1, 1)      ' move and resize the range of RC3 to include only the cell under column I        
        I = 0   ' initilize the rows deleted counter

            Do While (Range(RC3).Value <> "" And I < 30)    ' as long as RC points to a empty cell and we haven't removed more then 30 rows keep removing rows                                                             

                If (range(RC2).Value = "Permit & Design" Or range(RC2).Value = "Miscellanious") Then 'don't delete row if Permit & Design or Miscellanious is in the cell under column D

                  I = 30        ' escape the loop if true

                Else

                  Selection.EntireRow.Delete      ' delete the selected row if false

                  I = I + 1                       ' add 1 to the counter

                End If        

            Loop                                   ' Go back to the start of the Do While

    Next Rws                    ' go back to the For Each and put the next row in Rws

    .ScreenUpdating = True      ' now update the interface with the changes

end with

end sub

RC3 is already a Range, so no need for range(range), just RC3.Value — Slai
– Slai, Commented May 10, 2017 at 20:16
Selection.EntireRow.Delete seems to be deleting an unspecified, arbitrary user-selected row. Probably not what you intend. Why work off Selection anyway? — Mathieu Guindon
– Mathieu Guindon, Commented May 10, 2017 at 20:19
@Mat'sMug Thanks! I forgot to change that back to RC2.EntireRow.Delete... — Austin Huddleson
– Austin Huddleson, Commented May 10, 2017 at 20:34

Community · Accepted Answer · 2017-05-23 12:10:05Z

Do While (Range(RC3).Value <> "" And I < 30)

RC3 is a Range object. What Range(SomeRangeObject) does is really Range(SomeRangeObject.Value), so unless RC3.Value contains a valid range address string, that unqualified Range call is going to blow up.

Note unqualified: your code implicitly works off the ActiveSheet:

Set Allrws = Range("A2:S" & CStr(LastRow))

Whenever Range is used like this, it's implicitly doing ActiveSheet.Range, through the _Global hidden module.

Unqualified Range, Cells, Rows, Columns and Names calls are all implicitly referring to the ActiveSheet, and the misunderstanding of this fact is the reason behind every single "Related" question in the side bar (the ones I checked anyway), and there are thousands more of the same on this site: it's an extremely common source of bugs. So, qualify worksheet member calls and avoid problems.

Your code happens to work (well, given the above modification). If the With ActiveSheet block was changed to With Sheet12, you would start seeing issues stemming from all the unqualified Range calls.

Collectives™ on Stack Overflow

Excel VBA Method 'Range' of object'_global' failed error 1004

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related