Solved: Check if sheet is blank

[Djblois]

I would like to cheak if a sheet is blank or not and the run code on the sheet if it isn't blank. I know how to check if a cell is blank but not a whole worksheet.

[lucas]

I think something like this will work Daniel:
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
Else

[Djblois]

Lucas

that worked perfectly thank you

[lucas]

Yoinked it from Joseph(Malik641)
I use it with his combine workbooks code excluding blank sheets.

[mdmackillop]

... or

Sub Blank()
If Application.WorksheetFunction.CountBlank(ActiveSheet.Cells) = _
Rows.Count * Columns.Count Then
MsgBox "sheet is blank"
End If
End Sub

[mdmackillop]

BTW, this is probably better carried out as a function. You can use either version of the code to check.


Sub TestBlanks()
Dim sh As Worksheet
For Each sh In Worksheets
If IsBlank(sh) Then MsgBox sh.Name & " is blank"
Next
'or
For Each sh In Worksheets
If IsBlank2(sh) Then MsgBox sh.Name & " is blank"
Next
End Sub

Function IsBlank(sh As Worksheet)
If Application.WorksheetFunction.CountBlank(sh.Cells) = _
Rows.Count * Columns.Count Then
IsBlank = True
End If
End Function

Function IsBlank2(sh As Worksheet)
Set LastCell = sh.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then IsBlank2 = True
End Function

[xeterson]

BTW, this is probably better carried out as a function. You can use either version of the code to check.


Sub TestBlanks()
Dim sh As Worksheet
For Each sh In Worksheets
If IsBlank(sh) Then MsgBox sh.Name & " is blank"
Next
'or
For Each sh In Worksheets
If IsBlank2(sh) Then MsgBox sh.Name & " is blank"
Next
End Sub

Function IsBlank(sh As Worksheet)
If Application.WorksheetFunction.CountBlank(sh.Cells) = _
Rows.Count * Columns.Count Then
IsBlank = True
End If
End Function

Function IsBlank2(sh As Worksheet)
Set LastCell = sh.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then IsBlank2 = True
End Function