How to know whether sheet1 contains any data in it?

K

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
 
L

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!
 
T

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).
 
B

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
 
L

Leo Heuser

Bob Flanagan said:
You can't use UsedRange on an empty worksheet - an error will occur.
Click to expand...
Which version?

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

LeoH
 
L

Leo Heuser

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

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