Detect empty Excel sheet

[baltman]

Is there a quick function to determine if an Excel sheet is empty?

Thanks,
Brian

 

[gijsterbeek]

It's NOT fast (minutes) but:

Sub EmptySheet()
For r = 1 To 65355
For c = 1 To 256
If Worksheets(1).Cells(r, c) <> "" Then e = e + 1
Next c
Next r
If e > 0 Then MsgBox ("Not Empty") Else MsgBox ("Empty sheet!!")
End Sub

 

[Zathras]

I don't know of a quick function but you can experiment with this:

Option Explicit

Sub test()
  If isemptysheet Then
    MsgBox "empty"
  Else
    MsgBox "not empty"
  End If
End Sub
Function isemptysheet() As Boolean

  If ActiveSheet.UsedRange.Address = "$A$1" _
           And [A1] = "" Then
    isemptysheet = True
  Else
    isemptysheet = False
  End If
  
End Function

 

[PHV]

A starting point:
Dim c As Range
Set c = Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart)
If c Is Nothing Then MsgBox "Empty sheet"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[baltman]

I'm just getting an error when I try to run some import code on an empty sheet. I'm cycling through every sheet of every workbook in a directory.

My current solution is to test the value of A1 and replacing it with 'X' if it is null and then filter those sheets out later when I import the sheet into my database table. That seems like a faster solution.

Just thought it might be the kind of function that might have been build into VBA.

Thanks,
Brian

 

[Loomah]

Brian
I'm sure you have your solution by now but here's yet another way of testing for empty sheets:-

If WorksheetFunction.CountA(Cells) = 0 Then MsgBox "EMPTY SHEET"

Happy Friaday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​