Testing for an empty worksheet

H

Highlystrung

What is the best way to test for an empty worksheet? I have been using a
count on rows and capturing the error but I assume there is a much simpler
way to do it?
 
O

OssieMac

Hi Neil,

One way. I am interested if anyone has other options.

If WorksheetFunction.CountA(Cells()) = 0 Then
MsgBox "Worksheet is empty"
Else
MsgBox "worksheet is not empty"
End If
 
T

The Code Cage Team

I'm not sure the CountA function will account for formulae, however this
looks over the entire used range!

Sub empty_ws()
Dim Sh As Worksheet
For Each Sh In Sheets
If Sh.UsedRange = 0 Then
MsgBox Sh.Name & " is blank"
End If
Next Sh
End Sub


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
 
M

Mike H

Another,

If IsEmpty(ActiveSheet.UsedRange) Then
MsgBox "Empty"
Else
MsgBox "Used "
End If

Mike
 
G

Gary''s Student

If the cells in a worksheet contained only Comments, would you consider it
empty?
If a worksheet contained only embedded Objects, would you consider it empty?
 
P

Peter T

If Sh.UsedRange = 0 Then

If the count of the UR is two or more cells the above will fail.

If the only cell on the sheet, the UR, contains a value that evaluate to 0,
the test will return true but incorrect result.

AFAIK CountA should be fine but just for interest here's another way -

Sub test()
Dim c As Range
Dim ws As Worksheet
Set ws = ActiveSheet

For Each ws In ActiveWorkbook.Worksheets
Set c = Nothing
Set c = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Debug.Print ws.Name, "Is Empty : " & (c Is Nothing)
Next

End Sub


The OP really needs to define what "empty worksheet" is intended to mean.
The above Find method and CountA will confirm either way if any cell has a
value or formula (on which point Mike H's will give incorrect result if the
sheet has multiple empty but formatted cells).

If the intention is no formats and no contents, check for a UsedRange count
of 1 an CountA = 0 ( or the Find Method). Even with these checks potentially
a sheet could be entirely empty other than a format in A1 (more work can
check even that but overkill)

Regards,
Peter T
 
P

Peter T

Good point

With ActiveSheet
bNoObjects = .DrawingObjects.Count = 0 And .Comments.Count = 0
End With

Regards,
Peter T
 
P

Peter T

ActiveX worksheet controls (and other embedded objects) are included in
..DrawingObjects.Count

AFAIK, all objects types on a sheet, except notably cell comments, are
included in the DrawingObjects collection

Regards,
Peter T
 
O

OssieMac

I have found UsedRange unreliable. Some formatting affects it. Example: try
re-setting the row width in an otherwise empty sheet and it thinks that it
has a UsedRange. Even when using UsedRange where there is actual data, empty
ranges can be included.

However, as per other posts here, it depends on what is defined as an empty
sheet. Seems that the User should decide and perhaps use multiple tests
depending on their specific needs.

Anyway I like to see to see lots of comment and feedback like we have here.
It all helps to improve one's knowledge.
 
D

Dave Peterson

I agree with the others that it really depends on what you mean by an empty
worksheet--could it have names that are used elsewhere? Could it be important
to formulas on other sheets? Could it be used in code?

But if I'm checking to see if there are any values in the worksheet, I check the
usedrange to see if its address is $A$1. Then I check to see if that cell is
empty.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.UsedRange.Address = "$A$1" Then
If IsEmpty(wks.Range("a1").Value) Then
MsgBox wks.Name & vbLf & "is probably(??) not used."
End If
End If
Next wks
 
H

Highlystrung

Yes, I was a little vague - I actually want to find if a worksheet is empty
below the top row which will have headers in - below this there will be data.
Can you help with this greater specificity as most of your offers work on the
basis of looking for a totally empty sheet
 
P

Peter T

Yes, I was a little vague

hmm, how about highly misleading !
(not to worry)

before suggesting anything could you clarify

- Top header row is in Row 1 (top contents row is not always in row-1)
- "Empty" does / does not mean empty of formats, objects and comments
- Data? there are no formula cells or if there are formulas (eg as part of a
template) they should / should not be considered as empty cells

Regards,
Peter T


Highlystrung said:
Yes, I was a little vague - I actually want to find if a worksheet is
empty
below the top row which will have headers in - below this there will be
data.
Can you help with this greater specificity as most of your offers work on
the
basis of looking for a totally empty sheet
Click to expand...
 
D

Dave Peterson

I would check to see if there were any values or formulas in rows 2 to whatever.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
with wks
if application.counta(.range("2:" & .rows.count)) = 0 then
MsgBox wks.Name & vbLf & "is probably(??) not used."
End If
end with
Next wks

Yes, I was a little vague - I actually want to find if a worksheet is empty
below the top row which will have headers in - below this there will be data.
Can you help with this greater specificity as most of your offers work on the
basis of looking for a totally empty sheet
Click to expand...
 
H

Highlystrung

Works a treat, many thanks
--
thanks, Neil


Dave Peterson said:
I would check to see if there were any values or formulas in rows 2 to whatever.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
with wks
if application.counta(.range("2:" & .rows.count)) = 0 then
MsgBox wks.Name & vbLf & "is probably(??) not used."
End If
end with
Next wks
Click to expand...
 
Top