CODEWITHSUNDEEP
excelvbaruntime-error
Kaylynn Enright
Kaylynn Enright

Reputation: 27

Run-time error '1004': Method 'Range' of object 'Global' failed, on some worksheets, but my code is able to run on most other worksheets?

I'm new to VBA, and am running code on several worksheets. My code runs fine through most of the worksheets, but then I get a a run-time error when it loops to a worksheet that contains data that I've exported from a database, that is not formatted as a standard Excel worksheet (there are numbers that are formatted as "custom"). Could this be causing the error, if Excel can't recognize the numbers in the worksheets as numbers that are part of the range due to the formatting of the worksheet? See the part of the code that is not working below. It works on most of the worksheets, but I get the error when it gets to the tabs that are not formatted in the same way as the other worksheets.

Dim rng As Range

With ActiveSheet.PageSetup
    Set rng = Range(.PrintArea)
End With

Upvotes: 2

Views: 126

Answers (1)

BigBen
BigBen

Reputation: 49998

From the PageSetup.PrintArea documentation:

Set this property to False or to the empty string ("") to set the print area to the entire sheet

If the print area isn't set, then the result of .PrintArea (a blank string) is not a valid Range reference:

Try the following:

With ActiveSheet.PageSetup
    If .PrintArea = vbNullString Then
        Set rng = ActiveSheet.UsedRange
    Else
        Set rng = ActiveSheet.Range(.PrintArea)
    End If
End With

Upvotes: 3

Related Questions