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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

Ricky Morris

Active Member
Joined
Mar 31, 2002
Messages
363
Try:

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

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

eanderso

New Member
Joined
Sep 23, 2002
Messages
8

ADVERTISEMENT

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
 

XL-Dennis

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

JFitz

New Member
Joined
Nov 11, 2016
Messages
1
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
 

Forum statistics

Threads
1,144,733
Messages
5,725,981
Members
422,652
Latest member
Elnene1

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
Top