ExcelBanter - Testing for empty sheet

Define 'empty'.

Some might consider the result of a formula as =A1 + B1 with a result of
zero as "empty", but of course it isn't.

Here's one test I came up with, someone else may come up with a better one:

If Activesheet.UsedRange.Cells.Count = 1 And _
IsEmpty(Range(ActiveSheet.UsedRange.Address)) Then
MsgBox "Empty Sheet"
Exit Sub
End If

to make doubly sure, you could put this right after the above

If Application.WorksheetFunction.CountBlank(ActiveShe et.UsedRange) = _
ActiveSheet.UsedRange.Cells.Count Then
MsgBox "Sheet is effectively empty."
End If

To explain the first one: on a brand new sheet UsedRange will return a cells
count of 1 even though you've never put anything in it, and by testing that
one cell's contents you can confirm whether or not it is truly an empty
sheet. But the results of a math formula of any type are going to make it
appear not-empty.

Hope this helps.

"Stu W" wrote:

What's the easiest method to test for an empty worksheet?