RunTime Error 91 Object variable or With block variable not set

Question

I am new to VBA for excel and I am stuck with a little problem. I have a list of data and I have two task I want to complete.

The first will delete rows with any values in the J column that is equal to 0;

The second part: I want to loop through the rows left over and delete any rows that the value in I column is greater than the value In J column.

However I'm getting the following error: VBA RunTime Error 91 Object variable or With block variable not set

This is on the line that states:

For rw = .Cells(rows.Count, "E").End(xlUp).Row To 2 Step -1

The full code is

Private Sub clearupdata_Click()

Dim rows As Range, OSQty As Range, value As Long, rw As Long

Set OSQty = Range("j2")

Do Until OSQty.value = ""
    value = Val(OSQty.value)
    If (value = 0) Then
        If rows Is Nothing Then
            Set rows = OSQty.EntireRow
        Else
            Set rows = Union(OSQty.EntireRow, rows)
        End If
    End If
    Set OSQty = OSQty.Offset(1)
Loop

If Not rows Is Nothing Then rows.Delete

With Worksheets(1)   '<~~ worksheet ~~>
    For rw = .Cells(rows.Count, "E").End(xlUp).Row To 2 Step -1
         If .Cells(rw, "I").Value2 > .Cells(rw, "J").Value2 Then
             .rows(rw).EntireRow.Delete
         End If
    Next rw
End With

End Sub

Using rows as a variable name for a Range object is not a good idea because there is already a Rows property defined on the Application, Worksheet and Range objects — barrowc
– barrowc, Commented Oct 16, 2015 at 1:33
as barrowc said, rows.count might think its the range variable named rows. i tried rename the range it works — Patrick Lepelletier
– Patrick Lepelletier, Commented Oct 17, 2015 at 17:34

Raugmor · Accepted Answer · 2015-10-15 14:04:27Z

1

just change:

For rw = .Cells(rows.Count, "E").End(xlUp).Row To 2 Step -1

to

For rw = .Cells(.rows.Count, "E").End(xlUp).Row To 2 Step -1

the difference is when you call the function from a different sheet the rows.count is not for the same sheet so it falls over.

answered Oct 15, 2015 at 14:04

Raugmor

5665 silver badges12 bronze badges

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

2 Comments

mielk Over a year ago

Solution is ok, but explanation should be slightly different. It has nothing to do with different sheet, rows.count without dot will never work, since .Rows is a method of class Sheet and always must be preceded with an object of this class.

Raugmor Over a year ago

fair enough. I actually thought if sheet in this kind of a case is not specified it takes activesheet by default and I assumed it will run ok, which I just checked it doesn't.

Patrick Lepelletier · Accepted Answer · 2015-10-17 17:42:09Z

0

Dim rows2 As Range

won't compromise later with .Cells(.rows.Count, "E").End(xlUp).Row,

because rows.count would trigger the error, +1 to barrowc

answered Oct 17, 2015 at 17:42

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

Collectives™ on Stack Overflow

RunTime Error 91 Object variable or With block variable not set

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related