CODEWITHSUNDEEP
vbaexcelruntime-error
aLearningLady
aLearningLady

Reputation: 2088

"Application Defined or Object Defined Error" - Able to run script on some PCs but not others

I'm writing a VBA Add-in for a customer. I tested the code on my computer and a coworker's computer and it executed to perfection. However, when testing on the customer's computer, I get an error on this line

 ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select

From what I've read, this may be due to the fact that .select is used, however, I'm not quite sure what to substitute.

Below is the whole block:

ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$G2>30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Color = -16751204
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 10284031
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

Any ideas as to why this error is occurring? The entire Sub is quite lengthy, but I'd be happy to provide it if that would be helpful.

Upvotes: 1

Views: 1310

Answers (2)

user4039065
user4039065

Reputation:

Putting aside the use of ActiveSheet property and .Select for a moment, this:

With ActiveSheet.UsedRange
    .Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Select
End With

... is not the same as this:

With ActiveSheet.UsedRange
    .Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1, 0).Rows.Select
End With

If you run into a situation where Excel thinks the .Usedrange extends all the way to the bottom of the worksheet, the former is trying to push its last row off the worksheet and you will generate this error. The latter resizes first so the range to be offset is already one row less and you will not get the error.

While having a worksheet filled to the bottom row is not impossible, it is more likely that it is due to a value that was mistakenly put into the last row then deleted or formatting down to the last row. Neither situation is desirable.

fwiw, I generally prefer the Range.CurrentRegion property instead of the Worksheet.UsedRange property for blocks of data with no 'islands' as rogue 'last cells' are ignored unless there actually is a value in the bottom row..

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Try this, it works fine in my Excel (2010):

Sub tester()
Dim totalRows As Long
totalRows = ActiveSheet.UsedRange.Rows.Count
With ActiveSheet.UsedRange.Offset(1, 0).Resize(totalRows - 1).Rows

    .FormatConditions.Add Type:=xlExpression, Formula1:="=$G2>30"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority

    With .FormatConditions(1)
        .Font.Color = -16751204
        .Font.TintAndShade = 0
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 10284031
        .Interior.TintAndShade = 0
        .StopIfTrue = True
    End With

End With

End Sub

Also, you can see how to limit/remove the use of .select.

If that still gives you an error, replace totalRows = Activesheet... with totalRows = activesheet.cells(1048576,1).End(xlup).Row Note that this assumes Column A (1 in that range) has data that goes to the last row. If not, choose another column (B = 2, C=3,D=4,etc.).

Upvotes: 1

Related Questions