Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2011, 10:25 AM
Authorized User
 
Join Date: Oct 2011
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
Post Check if the excel document is empty

Hi,

Is there any vb code which can verify if the excel sheet is empty?
 
Old December 2nd, 2011, 12:03 AM
Registered User
 
Join Date: Dec 2011
Posts: 3
Thanks: 0
Thanked 1 Time in 1 Post
Default

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!
 
Old December 2nd, 2011, 12:17 AM
Registered User
 
Join Date: Dec 2011
Posts: 3
Thanks: 0
Thanked 1 Time in 1 Post
Smile A bit hasty

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
The Following User Says Thank You to Sisyphus For This Useful Post:
anandbabu65 (December 2nd, 2011)
 
Old December 2nd, 2011, 06:23 AM
Authorized User
 
Join Date: Oct 2011
Posts: 10
Thanks: 1
Thanked 0 Times in 0 Posts
Post

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
 
Old December 6th, 2011, 10:19 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordset Empty Check Coby Access VBA 2 April 27th, 2007 04:09 PM
best way to check for variable being empty crmpicco Classic ASP Basics 3 March 28th, 2006 12:11 PM
How to check if you have an empty array Ciarano VB How-To 9 March 31st, 2004 09:17 AM
check if empty hosefo81 Javascript How-To 12 March 9th, 2004 08:43 AM
How to check if collection is empty Ciarano Beginning VB 6 2 March 5th, 2004 08:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.