VBA - .UsedRange.Delete on multiple sheets

[Phosphonothioic]

Trying to set up a basic snipet of code to cycle through an array of sheets and delete the used range if the sheet is not empty.

I'm getting an Overflow error. Where am I going wrong?

Sub logicaltest()
Dim LastRow             As Long
Dim rememberCalcMode    As Long
Dim ShtArr
Dim ShtArrCounter       As Long
Dim HeadArr
    ShtArr = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    
    For ShtArrCounter = LBound(ShtArr) To UBound(ShtArr)
        With Sheets(ShtArr(SheetArrCounter))
            If Application.WorksheetFunction.CountBlank(.Cells) = _
                Rows.Count * Columns.Count Then
                'Do Nothing
            Else
                .UsedRange.Delete
            End If
        End With
    Next ShtArrCounter
 
...code

 

[Jonmo1]

why bother testing if the sheet is empty or not...
Just delete the used range..

For ShtArrCounter = LBound(ShtArr) To UBound(ShtArr)
    With Sheets(ShtArr(SheetArrCounter))
        .UsedRange.Delete
    End With
Next ShtArrCounter

 

[Norie]

Why not try some other method to see if the sheet is blank?

Or even delete the usedrange if it is blank.

 

[wigi]

Consider:

Sub logicaltest()
Dim LastRow             As Long
Dim rememberCalcMode    As Long
Dim ShtArr
Dim ShtArrCounter       As Long
Dim HeadArr

    ShtArr = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    
    For ShtArrCounter = 0 To UBound(ShtArr)
        Sheets(ShtArr(SheetArrCounter)).UsedRange.Clear
    Next
 
End Sub

 

[Phosphonothioic]

why bother testing if the sheet is empty or not...
Just delete the used range..

For ShtArrCounter = LBound(ShtArr) To UBound(ShtArr)
    With Sheets(ShtArr(SheetArrCounter))
        .UsedRange.Delete
    End With
Next ShtArrCounter

If the sheet is blank, I don't want to run the rest of the macro, if it isn't blank, obviously, I want to run the macro. I used .usedrange.delete just as a filler.

How would I go about checking to see if it's blank? If it is, I want to advance to the next sheet?

 

[andrewkard]

Hi
This code works in my file good.

 

[wigi]

Please check the codes in this topic, they are all relevant.

 

[Jonmo1]

If the sheet is blank, I don't want to run the rest of the macro, if it isn't blank, obviously, I want to run the macro. I used .usedrange.delete just as a filler.

How would I go about checking to see if it's blank? If it is, I want to advance to the next sheet?

OK, fair enough..
In the future, post what you're actually doing...don't oversimplify for our benefit..

The problem is that in XL2007 or higher, there are over a million rows
And Rows.Count * Columns.Count results in a number that is larger than the Long Datatype can handle..
Even though it's not using any variables, but it's trying to do the calculation as a long..

Interestingly, this works..

Dim x As Double
Dim y As Double
Dim z As Double
x = Rows.Count
y = Columns.Count
z = x * y

But anyway, there are far better ways to test if the sheet is empty..

If the Sheet is empty, then it's UsedRange is $A$1
So you only need to test if the UsedRange.Address = "$A$1"
AND
ISEMPTY(Range("A1"))


So,

If .UsedRange.Address = "$A$1" AND IsEmpty(.Range("A1")) Then

 

[Norie]

What other code?