CODEWITHSUNDEEP
vbaexcel
sifar
sifar

Reputation: 1148

How to Verify WorkSheets in a GROUP are empty or contain no data?

I would like to find a way to check if any sheets in sheets group are empty or contain data.

I tried using this, but doesn't seem to work.

.Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).select
If WorksheetFunction.CountA(Selection.UsedRange.Cells) = 0 Then 
  MsgBox "All Sheets empty!"
  Exit Sub
End If

Upvotes: 0

Views: 150

Answers (2)

chris neilsen
chris neilsen

Reputation: 53136

There are two possibilities:

If sheets "1" to "9" are adjacent with no other sheets in between, use:

Sub Demo1()
    With ThisWorkbook
        If Evaluate("=COUNTA('[" & .Name & "]1:9'!1:" & .Worksheets("1").Rows.Count & ")") = 0 Then
            MsgBox "All Sheets empty!"
        Else
            MsgBox "All Sheets NOT empty!"
        End If
    End With
End Sub

If sheets "1" to "9" are NOT adjacent, or have other sheets in between, use:
(or just use this version anyway)

Sub Demo2()
    Dim ws As Worksheet
    Dim tot As Long
    For Each ws In ThisWorkbook.Worksheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9"))
        tot = tot + WorksheetFunction.CountA(ws.Cells)
    Next
    If tot = 0 Then
        MsgBox "All Sheets empty!"
    Else
        MsgBox "All Sheets NOT empty!"
    End If
End Sub

Upvotes: 1

sifar
sifar

Reputation: 1148

for some reason, i am able to check if sheet is empty if i just use "Cells". Don't know why this works as its not referencing any of the grouped sheets. May fail if other workbooks are also open.

Sub SheetsEmpty()
With ThisWorkbook
    ' check if sheets are empty.
    .Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9")).Select
    If WorksheetFunction.CountA(Cells) = 0 Then Exit Sub
End With
End Sub

if i add a Watch to this "WorksheetFunction.CountA(Cells)" in watch window, it shows only a single count of cells that are non-empty and not for all 9 sheets. I guess that is because the sheets are grouped.

any ideas on this behaviour?

Upvotes: 1

Related Questions