Is Sheet Empty and unused

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi all,

I was thinking is there a methode in VBA to see if a sheet has been used or
that it is empty so that i can remove it from the workbook knowing i do not
loose data or better rename it an put the next bunch of data comming?

Thx,
Ludovic
 
Try this macro. HTH Otto
Sub IsSheetEmpty()
If Application.CountA(Cells) = 0 Then
MsgBox "It's empty."
Else
MsgBox "It's not empty."
End If
End Sub
 
Unfortunatly I can't use this because i like to use the code from Access VBA
using late binding and that does not accept the 'Cells' in '.CountA(Cells)'

Are there any other methods which would be suitable to be used from Access.
I like to check if there are any empty sheets in the workbook to which i can
dump data, this before I just add a sheet to the collection.

Cheers,
Ludovic
 
I've not controlled Excel from another program before, so this is just a
guess... what about if you explicitly qualify the Cells property with
ActiveSheet property?

If Application.CountA(ActiveSheet.Cells) = 0 Then
 
Here is an all VBA method to do what you want...

If ActiveSheet.Cells.Find(What:="*", LookIn:=xlFormulas) Is Nothing Then
MsgBox "All cells are empty"
Else
MsgBox "There is data on this worksheet"
End If

Note that this also uses ActiveSheet.Cells, but it does not try to use it
inside a worksheet function call. By the way, in both this and my previous
message, I am assuming that you are going to "vector" your calls to the
Excel objects through the variable that you have set up for referencing
Excel.
 
For the OP:

....and don't forget to replace any Excel constants such as xlFormulas with
their values.

Tim
 
Thanks for all advices. Finaly I managed like this:-

Dim objActiveWkbk As Object
Dim objXL As Object

Set objXL = CreateObject("Excel.Application")
Set objActiveWkbk = objXL.Application.ActiveWorkbook

If objXL.Application.CountA(objActiveWkbk.ActiveSheet.usedrange) = 0 then
..........


Cheers,
Ludovic
 
Back
Top