This will not detect any named ranges, conditional formatting, data
validation etc, so depends what you mean by "empty sheet".
NickHK
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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
> >
> >
> > "(E-Mail Removed)" 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
> > >
> > >
|
Similar Threads
