Check to see if a worksheet is blank in VBA

eanderso

New Member
Joined
Sep 23, 2002
Messages
8
I'm writing a macro to remove empty (blank) worksheets from a workbook. I can loop through the worksheets collection fine.

Does anyone know of there is a simple way to check if a given sheet is blank?

Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
Try:

if activesheet.usedrange.address = "$A$1" and range("A1") = "" then msgbox activesheet.name & " is blank"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
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.
 
Upvote 0
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
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.
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,573
Members
450,493
Latest member
Woejeber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top