I need to detect a empty excel sheet by VBA.

I know that CountA(Activesheet) = 0 and Activesheet.Shapes.Count = 0 is
Useful. But I found that it is not sufficient. For example, if I just
change the background color and the border of Cell A1. The code will
return true even the Activesheet is not empty at all.

I also tried about the code for Activesheet.UsedRange. But I found that
if the sheet is empty, it will always return A1:A1. But again, it is
not sufficient. At least it cannot pass the above test case.

Could any one tell me how to test a excel sheet is empty or not in all
cases? I think it is possible, because some Excel Add-Ins can do that.

Thank you very much.

Stephen Lai

Dear Michael,

Thank you for your nice answer.

But what I want is that "The function return true if and only if the
sheet is empty".
What I mean by empty is if I print the sheet, it will has something to
print.

Therefore, if the sheet is just changed cell A1's border or A1's
background color, it should also return false. Because the sheet is not
empty at all.

Again, thank you for your answer.

Stephen Lai


Michael ¼g¹D¡G

Use the code below and if the RealLastRow and RealLastColumn are equal to 0,
then you have an empty spreadsheet!..






Sub DeleteUnusedFormats()

'This routine will delete formatted but unused rows and columns
Dim lLastRow, lLastColumn As Long
Dim RealLastRow, lRealLastColumn As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AlertBeforeOverwriting = False
End With


With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With
Set RngFoundCell = Range("A1", Cells(lLastRow, lLastColumn)).Find(What:="*")
If RngFoundCell Is Nothing Then
Cells.Delete
Else
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByColumns, xlPrevious).Column
On Error Resume Next
If lRealLastRow < lLastRow And lLastRow < "65536" Then
Range(Cells(lLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
.EntireColumn.Delete
End If
End If


" wrote:

I need to detect a empty excel sheet by VBA.

I know that CountA(Activesheet) = 0 and Activesheet.Shapes.Count = 0 is
Useful. But I found that it is not sufficient. For example, if I just
change the background color and the border of Cell A1. The code will
return true even the Activesheet is not empty at all.

I also tried about the code for Activesheet.UsedRange. But I found that
if the sheet is empty, it will always return A1:A1. But again, it is
not sufficient. At least it cannot pass the above test case.

Could any one tell me how to test a excel sheet is empty or not in all
cases? I think it is possible, because some Excel Add-Ins can do that.

Thank you very much.

Stephen Lai

Stephen,
May be get the number of pages that would be printed. If 0 return True:
Worksheets(3).Select
MsgBox ExecuteExcel4Macro("Get.Document(50)")

NickHK

wrote in message
oups.com...
Dear Michael,

Thank you for your nice answer.

But what I want is that "The function return true if and only if the
sheet is empty".
What I mean by empty is if I print the sheet, it will has something to
print.

Therefore, if the sheet is just changed cell A1's border or A1's
background color, it should also return false. Because the sheet is not
empty at all.

Again, thank you for your answer.

Stephen Lai


Michael ¼g¹D¡G

Use the code below and if the RealLastRow and RealLastColumn are equal to
0,
then you have an empty spreadsheet!..






Sub DeleteUnusedFormats()

'This routine will delete formatted but unused rows and columns
Dim lLastRow, lLastColumn As Long
Dim RealLastRow, lRealLastColumn As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AlertBeforeOverwriting = False
End With


With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With
Set RngFoundCell = Range("A1", Cells(lLastRow,
lLastColumn)).Find(What:="*")
If RngFoundCell Is Nothing Then
Cells.Delete
Else
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByColumns, xlPrevious).Column
On Error Resume Next
If lRealLastRow < lLastRow And lLastRow < "65536" Then
Range(Cells(lLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
.EntireColumn.Delete
End If
End If


" wrote:

I need to detect a empty excel sheet by VBA.

I know that CountA(Activesheet) = 0 and Activesheet.Shapes.Count = 0 is
Useful. But I found that it is not sufficient. For example, if I just
change the background color and the border of Cell A1. The code will
return true even the Activesheet is not empty at all.

I also tried about the code for Activesheet.UsedRange. But I found that
if the sheet is empty, it will always return A1:A1. But again, it is
not sufficient. At least it cannot pass the above test case.

Could any one tell me how to test a excel sheet is empty or not in all
cases? I think it is possible, because some Excel Add-Ins can do that.

Thank you very much.

Stephen Lai

Dear NickHK,

Thank you very much. It works. ^^"

Stephen Lai

NickHK 寫�：

Stephen,
May be get the number of pages that would be printed. If 0 return True:
Worksheets(3).Select
MsgBox ExecuteExcel4Macro("Get.Document(50)")

NickHK

wrote in message
oups.com...
Dear Michael,

Thank you for your nice answer.

But what I want is that "The function return true if and only if the
sheet is empty".
What I mean by empty is if I print the sheet, it will has something to
print.

Therefore, if the sheet is just changed cell A1's border or A1's
background color, it should also return false. Because the sheet is not
empty at all.

Again, thank you for your answer.

Stephen Lai


Michael ¼g¹D¡G

Use the code below and if the RealLastRow and RealLastColumn are equal to
0,
then you have an empty spreadsheet!..






Sub DeleteUnusedFormats()

'This routine will delete formatted but unused rows and columns
Dim lLastRow, lLastColumn As Long
Dim RealLastRow, lRealLastColumn As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AlertBeforeOverwriting = False
End With


With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With
Set RngFoundCell = Range("A1", Cells(lLastRow,
lLastColumn)).Find(What:="*")
If RngFoundCell Is Nothing Then
Cells.Delete
Else
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByColumns, xlPrevious).Column
On Error Resume Next
If lRealLastRow < lLastRow And lLastRow < "65536" Then
Range(Cells(lLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
.EntireColumn.Delete
End If
End If


" wrote:

I need to detect a empty excel sheet by VBA.

I know that CountA(Activesheet) = 0 and Activesheet.Shapes.Count = 0 is
Useful. But I found that it is not sufficient. For example, if I just
change the background color and the border of Cell A1. The code will
return true even the Activesheet is not empty at all.

I also tried about the code for Activesheet.UsedRange. But I found that
if the sheet is empty, it will always return A1:A1. But again, it is
not sufficient. At least it cannot pass the above test case.

Could any one tell me how to test a excel sheet is empty or not in all
cases? I think it is possible, because some Excel Add-Ins can do that.

Thank you very much.

Stephen Lai

This will not detect any named ranges, conditional formatting, data
validation etc, so depends what you mean by "empty sheet".

NickHK

wrote in message
ps.com...
Dear NickHK,

Thank you very much. It works. ^^"

Stephen Lai

NickHK ??:

Stephen,
May be get the number of pages that would be printed. If 0 return True:
Worksheets(3).Select
MsgBox ExecuteExcel4Macro("Get.Document(50)")

NickHK

wrote in message
oups.com...
Dear Michael,

Thank you for your nice answer.

But what I want is that "The function return true if and only if the
sheet is empty".
What I mean by empty is if I print the sheet, it will has something to
print.

Therefore, if the sheet is just changed cell A1's border or A1's
background color, it should also return false. Because the sheet is not
empty at all.

Again, thank you for your answer.

Stephen Lai


Michael ¼g¹D¡G

Use the code below and if the RealLastRow and RealLastColumn are equal
to
0,
then you have an empty spreadsheet!..






Sub DeleteUnusedFormats()

'This routine will delete formatted but unused rows and columns
Dim lLastRow, lLastColumn As Long
Dim RealLastRow, lRealLastColumn As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.AlertBeforeOverwriting = False
End With


With Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = .Row
lLastColumn = .Column
End With
Set RngFoundCell = Range("A1", Cells(lLastRow,
lLastColumn)).Find(What:="*")
If RngFoundCell Is Nothing Then
Cells.Delete
Else
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
xlByColumns, xlPrevious).Column
On Error Resume Next
If lRealLastRow < lLastRow And lLastRow < "65536" Then
Range(Cells(lLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
End If
If lRealLastColumn < lLastColumn Then
Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
.EntireColumn.Delete
End If
End If


" wrote:

I need to detect a empty excel sheet by VBA.

I know that CountA(Activesheet) = 0 and Activesheet.Shapes.Count = 0
is
Useful. But I found that it is not sufficient. For example, if I just
change the background color and the border of Cell A1. The code will
return true even the Activesheet is not empty at all.

I also tried about the code for Activesheet.UsedRange. But I found
that
if the sheet is empty, it will always return A1:A1. But again, it is
not sufficient. At least it cannot pass the above test case.

Could any one tell me how to test a excel sheet is empty or not in all
cases? I think it is possible, because some Excel Add-Ins can do that.

Thank you very much.

Stephen Lai