How to check a blank excel sheet from VBA

[Ashishthaps]

from VBA how to check that a excel sheet is empty or not.

 

[OfficeHacker]

Hi Ashthaps,

Here's one solution:

Function IsWorksheetEmpty(ByVal strWorksheet As String) As Boolean
On Error Resume Next

Dim rng As Range

' Are there any cells with constants (non formulae)
Set rng =
Worksheets(strWorksheet).Cells.SpecialCells(xlCellTypeConstants, 23)

' If the rng variable is not empty - cells were found, so exit
If Not rng Is Nothing Then
GoTo ExitFunction
End If

' Are there any cells with formulae
Set rng =
Worksheets(strWorksheet).Cells.SpecialCells(xlCellTypeFormulas, 23)

' If the rng variable is not empty - cells were found, so exit
If Not rng Is Nothing Then
GoTo ExitFunction
End If

' no cells formula or constant cells were referenced so sheet empty
IsWorksheetEmpty = True

ExitFunction:
Exit Function

End Function

You can test the function like this:

Sub Test_IsWorksheetEmpty()
MsgBox IsWorksheetEmpty("Sheet1")
End Sub

However, I'm sure there must be a cleaner way. Maybe some-one else can come
up with a more graceful solution.

Marcus from Melbourne

 

[George Nicholson]

If you are only worried about data entries (not Shapes, etc.):

ActiveSheet.UsedRange.Address will return "$A$1" on a brand new sheet.
If any other cell has an entry it will return something else.

However, since A1 might have have an entry, you should check its contents
too.

Dim wks as Worksheet
Set wks = Worksheets("Sheet1")
If Len(wks.Range("A1"))=0 and wks.UsedRange.Address = "$A$1" Then
MsgBox wks.Name & " is empty."
End If

 

[Ashishthaps]

I have following solution but my problem is in comming versions of Excel
object library they could add more objects(apart from comments,charts,list
etc). I wants to be absolutly sure that there is nothing(No objects, formula
or text) on the worksheet.

my solution is like this
==================
Option Explicit
Sub main()
Dim xlApp As Excel.Application
Dim strMessage As String
Dim wb As workbook
Dim ws As Worksheet
Set xlApp = New Excel.Application
'Define a workbook which is to be investigated
Set wb = xlApp.Workbooks.Open("d:\ReimbursementDetail1000.xls")
'Define Sheet
Set ws = wb.Worksheets("Sheet1")

If (isWorkSheetEmpty(ws) = False) Then
strMessage = ws.Name & " Worksheet not empty"
Else
strMessage = ws.Name & " Worksheet empty"
End If
End Sub

Function isWorkSheetEmpty(ws As Worksheet) As Boolean


isWorkSheetEmpty = True
If ws.UsedRange.Count > 1 Then
isWorkSheetEmpty = False
Exit Function
'Check for first cell
ElseIf Not (ws.UsedRange.Item(1).Value = "") Then
isWorkSheetEmpty = False
Exit Function
End If
'Check for comments
If ws.Comments.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If
'Check for pivot tables
If ws.PivotTables.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If
'Check for query tables
If ws.QueryTables.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If
'Check for shapes
If ws.Shapes.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If
'check for chart objects
If ws.ChartObjects.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If

If ws.ListObjects.Count > 0 Then
isWorkSheetEmpty = False
Exit Function
End If


End Function
=======================