How to know whether sheet1 contains any data in it?

[kvenku]

Hi,
I have a VBA application where i need to know that sheets1 contain
any data on it

I tried this and this take a long time to execute..Can you help me out


For StrRow = 1 To Rows.Count
For StrCol = 1 To Columns.Count
If Len(Cells(StrCol, StrRow).Value) > 0 Then
MsgBox "Found"
Exit Sub
End If
Next
Nex

 

[Leo Heuser]

Hi

If Application.WorksheetFunction.CountA(Sheets("Sheet1"). _
UsedRange.Cells) = 0 Then
MsgBox "Sheet is empty"
Else
MsgBox "Sheet is not empty"
End If

Remember, that your feedback is appreciated!

 

[Tom Ogilvy]

Sub Checkfordata()
Dim rng As Range, rng1 As Range
On Error Resume Next
Set rng = Cells.SpecialCells(xlFormulas)
Set rng1 = Cells.SpecialCells(xlConstants)
On Error GoTo 0
If rng Is Nothing And rng1 Is Nothing Then
MsgBox "No Data"
Else
MsgBox "Data Found"
End If
End Sub

It could depend on your definition of having data. If you click in a cell
and hit the spacebar, the sheet will look empty, but this would say it found
data (but so would your original).

 

[Bob Flanagan]

if application.counta(cells) = 0 then
'no data
end if

You can't use UsedRange on an empty worksheet - an error will occur.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Leo Heuser]

You can't use UsedRange on an empty worksheet - an error will occur.

Which version?

Version 97, 2000, 2002 and 2003 all return A1
as the UsedRange.Address for an empty sheet.

LeoH

 

[kvenku]

Thanks Leo Heuser. Its working...Great

Regards
Venkatesh

 

[Leo Heuser]

You're welcome, Venkatesh, and thanks
for the feedback

(Leo will do. We are very informal in these groups