Check if the excel document is empty

anandbabu65 · December 1st, 2011, 10:25 AM

Hi,

Is there any vb code which can verify if the excel sheet is empty?

Sisyphus · December 2nd, 2011, 12:03 AM

This code will tell you:

Code:

Private Function SheetIsEmpty() As Boolean
    
    Dim Ws As Worksheet
    Dim Rng As Range
    Dim Fi As Range
    
    
    Set Ws = ActiveSheet
    With Ws
        Set Rng = Range(.Cells(1, 1), .Cells(.Rows.Count, .Columns.Count))
        Set Fi = .Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, _
                                  SearchDirection:=xlPrevious)
    End With
    SheetIsEmpty = (Fi Is Nothing)
End Function

Have a nice day!

Sisyphus · December 2nd, 2011, 12:17 AM

Sorry, I posted too quickly. Here is the improvement:-

Code:

Private Sub test()
    Debug.Print SheetIsEmpty(ActiveSheet)
End Sub

Private Function SheetIsEmpty(ByRef Ws As Worksheet) As Boolean
    
    Dim Rng As Range
    Dim Fi As Range
    
    With Ws
        Set Rng = Range(.Cells(1, 1), .Cells(.Rows.Count, .Columns.Count))
        Set Fi = Rng.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, _
                                  SearchDirection:=xlPrevious)
    End With
    SheetIsEmpty = (Fi Is Nothing)
End Function

In fact, the first post also works, but it doesn't use the Range first created.
Rng.Cells.Find will look in the range you set.
Ws.Cells.Find will look in the entire worksheet.
Since this is what you want, you don't need the range and you can shorten the code thus:

Code:

Private Function SheetIsEmpty(ByRef Ws As Worksheet) As Boolean
    
    Dim Fi As Range
    
    Set Fi = Ws.Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, _
                                        SearchDirection:=xlPrevious)
    SheetIsEmpty = (Fi Is Nothing)
End Function

anandbabu65 · December 2nd, 2011, 06:23 AM

Thanks for the reply.

I also found this code that can be used.

Code:

Public Function IsWSEmpty(ByRef wsName As String) As Boolean 
     
    Dim wb As Excel.Workbook 
    Dim ws As Excel.Worksheet 
     
    Set wb = ThisWorkbook 
    Set ws = wb.Worksheets(wsName) 
     
    If ws.UsedRange.Address = "$A$1" Then 
        IsWSEmpty = True 
    End If 
     
    Set ws = Nothing 
    Set wb = Nothing 
     
End Function

Shasur · December 6th, 2011, 10:19 PM

Anand

You can perhaps also check if A1 is empty in your function as usedrange will return $A$1 even if there is a value in A1

Cheers
Shasur