Check in on specific sheet (specific range) if isEmpty or not before closing workbook

[Gwhaou]

Hi,

Actually I'm trying to initialize my workbook before closing it (by deleting all the added data) save it then close it.
Before that I want to check if there is any data on a specific sheet before deleting and closing the workbook, so if there is any data on that particular sheet so i can stop the deleting and closing process.
I tried this code but even when the specifc sheet is Empty it close the workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'In case if that particular sheet is empty
'Delete all the data form other sheet then save and close the workbook
    If IsEmpty(Worksheets("DATA_SHEET").Range("A2:Q50")) = True Then

        Worksheets("SHEET1").Rows(2 & ":" & Worksheets("SHEET1").Rows.Count).Delete
        Worksheets("SHEET2").Rows(2 & ":" & Worksheets("SHEET2").Rows.Count).Delete
        ActiveWorkbook.Save
        ThisWorkbook.Saved = True

'In case that the particular sheet is not empty 
'Active that sheet and cancel  the closing process       
    Else

        Worksheets("DATA_SHEET").Activate
        MsgBox "Carful there is data on the actual sheet !"
      

    End If
    

End Sub

Every time i click on X for closing the workbook the code is opening the message box, even if there is data or not one the "DATA_SHEET"

Even when i Click on OK or X it close the work book.



I want some Help please

 

[6StringJazzer]

IsEmpty does not tell you whether the sheet is empty. It tells you if a variable is uninitialized. The way you are using it, it will always be FALSE. Try this:

    If WorksheetFunction.CountA(Worksheets("DATA_SHEET").Range("A2:Q50")) = 0 Then

 

[Gwhaou]

IsEmpty does not tell you whether the sheet is empty. It tells you if a variable is uninitialized. The way you are using it, it will always be FALSE. Try this:

    If WorksheetFunction.CountA(Worksheets("DATA_SHEET").Range("A2:Q50")) = 0 Then

Ok i'm gonna try that, thks for the reply

 

[Gwhaou]

Thank's Jeff, it works