How to know whether sheet1 contains any data in it?
 

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

--
Message posted from http://www.ExcelForum.com

How to know whether sheet1 contains any data in it?
 

Hi

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

Remember, that your feedback is appreciated!


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"kvenku " skrev i en meddelelse
...
Hi,
I have a VBA application where i need to know that sheets1 contains
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
Next


---
Message posted from http://www.ExcelForum.com/

How to know whether sheet1 contains any data in it?
 

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

--
Regards,
Tom Ogilvy



"kvenku " wrote in message
...
Hi,
I have a VBA application where i need to know that sheets1 contains
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
Next


---
Message posted from http://www.ExcelForum.com/

How to know whether sheet1 contains any data in it?
 

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

"kvenku " wrote in message
...
Hi,
I have a VBA application where i need to know that sheets1 contains
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
Next


---
Message posted from http://www.ExcelForum.com/

How to know whether sheet1 contains any data in it?
 

"Bob Flanagan" skrev i en meddelelse
...

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

How to know whether sheet1 contains any data in it?
 

Thanks Leo Heuser. Its working...Great

Regards
Venkatesh

--
Message posted from http://www.ExcelForum.com

How to know whether sheet1 contains any data in it?
 

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

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

--
Best Regards
LeoH


"kvenku " skrev i en meddelelse
...
Thanks Leo Heuser. Its working...Great

Regards
Venkatesh.