asked on

Excel VBA: check if sheet is empty

Hello experts,
I am trying to check if a sheet is empty.
I don't understand why I got an syntax error message related to MsgBox.
Thank you for your help.

Sub Display_Used_Range()


    Dim LRow As Long, LCol As Long

    Set oWS = ActiveSheet
     'Check if sheet is empty
     With oWS
        If Application.WorksheetFunction.CountA(oWS.UsedRange) = 0 And oWS.Shapes.Count = 0 Then
        MsgBox (oWS & " is empty. Unable to proceed", vbCritical)
        Exit Sub
        End If
    End With
   
    lRw = oWS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    Col = oWS.Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
    Set rRng = oWS.Range("A1", oWS.Cells(lRw, Col))

    MsgBox "Last Used Column Letter is " & Col & vbNewLine & "Last Used Row Number is " & lRw & vbNewLine & "Full Used Range is " & rRng.Address

Open in new window

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you Subodh.
I used option explicit. I didn't posted all variables as this procedure is part of my add-in and variables are reported at the top of the module. I forgot to report variables declared at the top of the module and I just posted procedure. I am sorry for that.

In regards to your proposal it works! You are right oWS is a worksheet so I need to use oWS.Name in the MsgBox.
Thank you again for this great advice!

Subodh Tiwari (Neeraj)

You're welcome Luis! Glad you found it helpful.