Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sheet1 data goes to sheet2 automaticaly ? | Excel Worksheet Functions | |||
Data in sheet1 to be put into sheets 2, 3, 4, 5, etc | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
copying data from sheet1 to sheet2 | Excel Worksheet Functions | |||
How To Retrieve Data from Sheet2 into Sheet1 | Excel Worksheet Functions |