Application-defined or object-defined error

  • Thread starter Thread starter Ken Loomis
  • Start date Start date
K

Ken Loomis

I get an error in this macro.

It is listed as

"Run-time error ('1004':

Application-defined or object-defined error"


The error is caused by this line:

.Range(Cells(4, 1), Cells(totalrows, 6)).Clear


Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(Cells(4, 1), Cells(totalrows, 6)).Clear
End If
End With

End Sub



I was certain this macro ran at least once.

Can someone please help with this?

Ken Loomis
 
Street Summary is probably not the active sheet when the macro runs. If that's
the case, you are trying to define a range on that sheet (Street Summary)
using references to cells on a different sheet, namely the ACTIVE sheet. Won't
work, of course.

I suspect you forgot the periods in front of the two Cells properties. Try it
like this and report back, please:

Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(.Cells(4, 1), .Cells(totalrows, 6)).Clear
End If
End With
End Sub
 
Thanks, Myrna.

That was it.

Ken Loomis

Myrna Larson said:
Street Summary is probably not the active sheet when the macro runs. If
that's
the case, you are trying to define a range on that sheet (Street Summary)
using references to cells on a different sheet, namely the ACTIVE sheet.
Won't
work, of course.

I suspect you forgot the periods in front of the two Cells properties. Try
it
like this and report back, please:

Sub clearStreetSummary()
Dim totalrows As Integer

With Sheets("Street Summary")
totalrows = .UsedRange.Rows.count + 2
If totalrows > 3 Then
.Range(.Cells(4, 1), .Cells(totalrows, 6)).Clear
End If
End With
End Sub
Click to expand...
 
Back
Top