Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
What is the best way to test for an empty worksheet? I have been using a
count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Hi Neil,
One way. I am interested if anyone has other options. If WorksheetFunction.CountA(Cells()) = 0 Then MsgBox "Worksheet is empty" Else MsgBox "worksheet is not empty" End If -- Regards, OssieMac "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
I'm not sure the CountA function will account for formulae, however this looks over the entire used range! Sub empty_ws() Dim Sh As Worksheet For Each Sh In Sheets If Sh.UsedRange = 0 Then MsgBox Sh.Name & " is blank" End If Next Sh End Sub -- The Code Cage Team Regards, The Code Cage Team www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7565 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Another,
If IsEmpty(ActiveSheet.UsedRange) Then MsgBox "Empty" Else MsgBox "Used " End If Mike "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
If the cells in a worksheet contained only Comments, would you consider it
empty? If a worksheet contained only embedded Objects, would you consider it empty? -- Gary''s Student - gsnu200810 "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
If Sh.UsedRange = 0 Then
If the count of the UR is two or more cells the above will fail. If the only cell on the sheet, the UR, contains a value that evaluate to 0, the test will return true but incorrect result. AFAIK CountA should be fine but just for interest here's another way - Sub test() Dim c As Range Dim ws As Worksheet Set ws = ActiveSheet For Each ws In ActiveWorkbook.Worksheets Set c = Nothing Set c = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _ LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Debug.Print ws.Name, "Is Empty : " & (c Is Nothing) Next End Sub The OP really needs to define what "empty worksheet" is intended to mean. The above Find method and CountA will confirm either way if any cell has a value or formula (on which point Mike H's will give incorrect result if the sheet has multiple empty but formatted cells). If the intention is no formats and no contents, check for a UsedRange count of 1 an CountA = 0 ( or the Find Method). Even with these checks potentially a sheet could be entirely empty other than a format in A1 (more work can check even that but overkill) Regards, Peter T "The Code Cage Team" wrote in message ... I'm not sure the CountA function will account for formulae, however this looks over the entire used range! Sub empty_ws() Dim Sh As Worksheet For Each Sh In Sheets If Sh.UsedRange = 0 Then MsgBox Sh.Name & " is blank" End If Next Sh End Sub -- The Code Cage Team Regards, The Code Cage Team www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=7565 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Good point
With ActiveSheet bNoObjects = .DrawingObjects.Count = 0 And .Comments.Count = 0 End With Regards, Peter T "Gary''s Student" wrote in message ... If the cells in a worksheet contained only Comments, would you consider it empty? If a worksheet contained only embedded Objects, would you consider it empty? -- Gary''s Student - gsnu200810 "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
How about Controls?
-- Gary''s Student - gsnu200810 "Peter T" wrote: Good point With ActiveSheet bNoObjects = .DrawingObjects.Count = 0 And .Comments.Count = 0 End With Regards, Peter T "Gary''s Student" wrote in message ... If the cells in a worksheet contained only Comments, would you consider it empty? If a worksheet contained only embedded Objects, would you consider it empty? -- Gary''s Student - gsnu200810 "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
ActiveX worksheet controls (and other embedded objects) are included in
..DrawingObjects.Count AFAIK, all objects types on a sheet, except notably cell comments, are included in the DrawingObjects collection Regards, Peter T "Gary''s Student" wrote in message ... How about Controls? -- Gary''s Student - gsnu200810 "Peter T" wrote: Good point With ActiveSheet bNoObjects = .DrawingObjects.Count = 0 And .Comments.Count = 0 End With Regards, Peter T "Gary''s Student" wrote in message ... If the cells in a worksheet contained only Comments, would you consider it empty? If a worksheet contained only embedded Objects, would you consider it empty? -- Gary''s Student - gsnu200810 "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
I have found UsedRange unreliable. Some formatting affects it. Example: try
re-setting the row width in an otherwise empty sheet and it thinks that it has a UsedRange. Even when using UsedRange where there is actual data, empty ranges can be included. However, as per other posts here, it depends on what is defined as an empty sheet. Seems that the User should decide and perhaps use multiple tests depending on their specific needs. Anyway I like to see to see lots of comment and feedback like we have here. It all helps to improve one's knowledge. -- Regards, OssieMac "Mike H" wrote: Another, If IsEmpty(ActiveSheet.UsedRange) Then MsgBox "Empty" Else MsgBox "Used " End If Mike "Highlystrung" wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
I agree with the others that it really depends on what you mean by an empty
worksheet--could it have names that are used elsewhere? Could it be important to formulas on other sheets? Could it be used in code? But if I'm checking to see if there are any values in the worksheet, I check the usedrange to see if its address is $A$1. Then I check to see if that cell is empty. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.UsedRange.Address = "$A$1" Then If IsEmpty(wks.Range("a1").Value) Then MsgBox wks.Name & vbLf & "is probably(??) not used." End If End If Next wks Highlystrung wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Yes, I was a little vague - I actually want to find if a worksheet is empty
below the top row which will have headers in - below this there will be data. Can you help with this greater specificity as most of your offers work on the basis of looking for a totally empty sheet -- thanks, Neil "Dave Peterson" wrote: I agree with the others that it really depends on what you mean by an empty worksheet--could it have names that are used elsewhere? Could it be important to formulas on other sheets? Could it be used in code? But if I'm checking to see if there are any values in the worksheet, I check the usedrange to see if its address is $A$1. Then I check to see if that cell is empty. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.UsedRange.Address = "$A$1" Then If IsEmpty(wks.Range("a1").Value) Then MsgBox wks.Name & vbLf & "is probably(??) not used." End If End If Next wks Highlystrung wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Yes, I was a little vague
hmm, how about highly misleading ! (not to worry) before suggesting anything could you clarify - Top header row is in Row 1 (top contents row is not always in row-1) - "Empty" does / does not mean empty of formats, objects and comments - Data? there are no formula cells or if there are formulas (eg as part of a template) they should / should not be considered as empty cells Regards, Peter T "Highlystrung" wrote in message ... Yes, I was a little vague - I actually want to find if a worksheet is empty below the top row which will have headers in - below this there will be data. Can you help with this greater specificity as most of your offers work on the basis of looking for a totally empty sheet -- thanks, Neil "Dave Peterson" wrote: I agree with the others that it really depends on what you mean by an empty worksheet--could it have names that are used elsewhere? Could it be important to formulas on other sheets? Could it be used in code? But if I'm checking to see if there are any values in the worksheet, I check the usedrange to see if its address is $A$1. Then I check to see if that cell is empty. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.UsedRange.Address = "$A$1" Then If IsEmpty(wks.Range("a1").Value) Then MsgBox wks.Name & vbLf & "is probably(??) not used." End If End If Next wks Highlystrung wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
I would check to see if there were any values or formulas in rows 2 to whatever.
Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets with wks if application.counta(.range("2:" & .rows.count)) = 0 then MsgBox wks.Name & vbLf & "is probably(??) not used." End If end with Next wks Highlystrung wrote: Yes, I was a little vague - I actually want to find if a worksheet is empty below the top row which will have headers in - below this there will be data. Can you help with this greater specificity as most of your offers work on the basis of looking for a totally empty sheet -- thanks, Neil "Dave Peterson" wrote: I agree with the others that it really depends on what you mean by an empty worksheet--could it have names that are used elsewhere? Could it be important to formulas on other sheets? Could it be used in code? But if I'm checking to see if there are any values in the worksheet, I check the usedrange to see if its address is $A$1. Then I check to see if that cell is empty. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.UsedRange.Address = "$A$1" Then If IsEmpty(wks.Range("a1").Value) Then MsgBox wks.Name & vbLf & "is probably(??) not used." End If End If Next wks Highlystrung wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for an empty worksheet
Works a treat, many thanks
-- thanks, Neil "Dave Peterson" wrote: I would check to see if there were any values or formulas in rows 2 to whatever. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets with wks if application.counta(.range("2:" & .rows.count)) = 0 then MsgBox wks.Name & vbLf & "is probably(??) not used." End If end with Next wks Highlystrung wrote: Yes, I was a little vague - I actually want to find if a worksheet is empty below the top row which will have headers in - below this there will be data. Can you help with this greater specificity as most of your offers work on the basis of looking for a totally empty sheet -- thanks, Neil "Dave Peterson" wrote: I agree with the others that it really depends on what you mean by an empty worksheet--could it have names that are used elsewhere? Could it be important to formulas on other sheets? Could it be used in code? But if I'm checking to see if there are any values in the worksheet, I check the usedrange to see if its address is $A$1. Then I check to see if that cell is empty. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.UsedRange.Address = "$A$1" Then If IsEmpty(wks.Range("a1").Value) Then MsgBox wks.Name & vbLf & "is probably(??) not used." End If End If Next wks Highlystrung wrote: What is the best way to test for an empty worksheet? I have been using a count on rows and capturing the error but I assume there is a much simpler way to do it? -- thanks, Neil -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TESTING FOR EMPTY ARRAY | Excel Programming | |||
Testing for empty sheet | Excel Programming | |||
empty cells - best way of testing for them | Excel Programming | |||
Testing for null or empty | Excel Programming | |||
testing for non-empty cells | Excel Programming |