Check to see if a worksheet is blank in VBA

eanderso,

Welcome to the board

Do You really need to check the whole worksheets?

Usually we do a search within a limited range.

Kind regards,
Dennis

Try:

if activesheet.usedrange.address = "$A$1" and range("A1") = "" then msgbox activesheet.name & " is blank"

Ok, just to add to Rick's answer.

Here´s a way to loop through the worksheets in active workbook and at the same time *avoid* activating each worksheets.

<PRE>
<FONT color=blue>Sub </FONT>test1()

<FONT color=blue>Dim </FONT>wbBook<FONT color=blue> As</FONT><FONT color=blue> Workbook</FONT>

<FONT color=blue>Dim </FONT>wsSheet<FONT color=blue> As</FONT> Worksheet



<FONT color=blue>Set </FONT>wbBook = ActiveWorkbook



<FONT color=blue>For </FONT>Each wsSheet In wbBook.Worksheets

<FONT color=blue>With </FONT>wsSheet

<FONT color=blue>If </FONT>.UsedRange.Address = "$A$1" And Range("A1") = "" Then

MsgBox wsSheet.Name & " is blank"

<FONT color=blue>End If</FONT>

<FONT color=blue>End With</FONT>

<FONT color=blue>Next </FONT>wsSheet

<FONT color=blue>End Sub</FONT>


</PRE>

Kind regards,
Dennis

Thanks to all for your help. I got it to work just like I wanted. Here's the routine.
-Eric


Public Sub RemoveBlankSheets()

Dim s As Worksheet
Dim sMsg As String
Dim bProcede As Boolean

sMsg = "It's a good idea to save the workbook before proceding." & _
vbCrLf & vbCrLf & "Do you want to save the workbook?"
Select Case MsgBox(sMsg, vbYesNoCancel + vbQuestion + vbDefaultButton1, "Removing Blank Sheets")
Case vbYes
bProcede = True
ActiveWorkbook.Save
Case vbNo
bProcede = True
Case vbCancel
bProcede = False
End Select

If bProcede Then
For Each s In ActiveWorkbook.Sheets
If s.UsedRange.Address = "$A$1" And s.Range("A1") = "" Then
Application.DisplayAlerts = False
s.Delete
Application.DisplayAlerts = True
End If
Next
End If

End Sub

Hi,

Funny, the below line create an error in my swedish version of XL (bug or feature or language specific...)

If s.UsedRange.Address = "$A$1" And s.Range("A1") = "" Then

That´s why I used above solution with the With-statement

Nevertheless, glad it worked out for You

Kind regards,
Dennis

I realize this is a very old post. However, I thought I may still be able to help someone looking to do this.

The easiest way I know to check for a blank sheet is by taking advantage of the CountA worksheet function.

To check a specific sheet


To loop through all sheets in a Workbook

JFitz said:

I realize this is a very old post. However, I thought I may still be able to help someone looking to do this.

The easiest way I know to check for a blank sheet is by taking advantage of the CountA worksheet function.

To check a specific sheet

Code:

Application.ScreenUpdating = False

Sheets("Sheet2").Select

     If WorksheetFunction.CountA(Cells) = 0 Then
          MsgBox ("Sheet2 is blank.", vbOKOnly + vbInformation)
     End If

End Sub

To loop through all sheets in a Workbook

Code:

Sub CheckForBlankWS()

Application.ScreenUpdating = False

Dim wBook As Workbook
Dim wSheet As Worksheet

Set wBook = ActiveWorkbook

For Each wSheet In wBook.Worksheets

   With wSheet
        If WorksheetFunction.CountA(Cells) = 0 Then
             MsgBox (wSheet.Name & " is blank.", vbOKOnly + vbInformation)
       End If
   End With

Next wSheet

End Sub

Click to expand...

This is definitely the best way. Excel keeps track of the number of used cells as you go, so this solution does not require any calculation. It's just a simple lookup.. Very fast and accurate.

XL-Dennis said:

Hi,

Funny, the below line create an error in my swedish version of XL (bug or feature or language specific...)

If s.UsedRange.Address = "$A$1" And s.Range("A1") = "" Then

That´s why I used above solution with the With-statement

Nevertheless, glad it worked out for You

Kind regards,
Dennis

Click to expand...

There's a minor flaw in this solution. Instead of using "s.Range("A1") = "" " use "isempty(sRange("A1")=true". If A1 contains non-text (e.g., a PictureInCell) you will get a runtime error. Using CountA, though is a better solution in my opinion.