Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
I need to construct a Range object that includes all cells with values,
formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
Have you tried something like this. I have a long macro running so can't
check it in Excel. Dim Area as range Set Area = Nothing on error resume next Set Area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) on error goto 0 If Area is nothing then ...do something else end if HTH, Barb Reinhardt "Jerry W. Lewis" wrote: I need to construct a Range object that includes all cells with values, formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
I'd use 3 range variables and combine them into 1 big one.
Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngComm As Range Dim RngTot As Range Set RngConst = Nothing Set RngForm = Nothing Set RngComm = Nothing Set RngTot = Nothing With ActiveSheet On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) Set RngComm = .Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 End With If RngConst Is Nothing Then If RngForm Is Nothing Then If RngComm Is Nothing Then 'do nothing Else Set RngTot = RngComm End If Else 'has formulas If RngComm Is Nothing Then Set RngTot = RngForm Else Set RngTot = Union(RngForm, RngComm) End If End If Else 'has constants If RngForm Is Nothing Then If RngComm Is Nothing Then Set RngTot = RngConst Else Set RngTot = Union(RngConst, RngComm) End If Else 'has formulas, too If RngComm Is Nothing Then Set RngTot = Union(RngConst, RngForm) Else Set RngTot = Union(RngConst, RngForm, RngComm) End If End If End If If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub ====== A long time ago, I saw a post by Tom Ogilvy that was pretty neat. Someone asked a similar question and his solution was to insert a new worksheet (or a worksheet in a new workbook) and use that to help. Something like: Option Explicit Sub testme2() Dim ActWks As Worksheet Dim TmpWks As Worksheet Dim Addr As String Dim RngTot As Range Set ActWks = ActiveSheet Set TmpWks = Workbooks.Add(1).Worksheets(1) Set RngTot = Nothing With ActWks On Error Resume Next Addr = "" Addr = .Cells.SpecialCells(xlCellTypeConstants).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeFormulas).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeComments).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = TmpWks.Cells.SpecialCells(xlCellTypeConstants).Add ress TmpWks.Parent.Close savechanges:=False If Addr = "" Then 'do noting Else Set RngTot = .Range(Addr) End If End With If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub I thought it was pretty slick. And if you decide to add more requirements, it's easier (I think) to modify. Jerry W. Lewis wrote: I need to construct a Range object that includes all cells with values, formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
Sure you didn't forget one, Dave?<g
"Dave Peterson" wrote: I'd use 3 range variables and combine them into 1 big one. Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngComm As Range Dim RngTot As Range Set RngConst = Nothing Set RngForm = Nothing Set RngComm = Nothing Set RngTot = Nothing With ActiveSheet On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) Set RngComm = .Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 End With If RngConst Is Nothing Then If RngForm Is Nothing Then If RngComm Is Nothing Then 'do nothing Else Set RngTot = RngComm End If Else 'has formulas If RngComm Is Nothing Then Set RngTot = RngForm Else Set RngTot = Union(RngForm, RngComm) End If End If Else 'has constants If RngForm Is Nothing Then If RngComm Is Nothing Then Set RngTot = RngConst Else Set RngTot = Union(RngConst, RngComm) End If Else 'has formulas, too If RngComm Is Nothing Then Set RngTot = Union(RngConst, RngForm) Else Set RngTot = Union(RngConst, RngForm, RngComm) End If End If End If If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub ====== A long time ago, I saw a post by Tom Ogilvy that was pretty neat. Someone asked a similar question and his solution was to insert a new worksheet (or a worksheet in a new workbook) and use that to help. Something like: Option Explicit Sub testme2() Dim ActWks As Worksheet Dim TmpWks As Worksheet Dim Addr As String Dim RngTot As Range Set ActWks = ActiveSheet Set TmpWks = Workbooks.Add(1).Worksheets(1) Set RngTot = Nothing With ActWks On Error Resume Next Addr = "" Addr = .Cells.SpecialCells(xlCellTypeConstants).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeFormulas).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeComments).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = TmpWks.Cells.SpecialCells(xlCellTypeConstants).Add ress TmpWks.Parent.Close savechanges:=False If Addr = "" Then 'do noting Else Set RngTot = .Range(Addr) End If End With If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub I thought it was pretty slick. And if you decide to add more requirements, it's easier (I think) to modify. Jerry W. Lewis wrote: I need to construct a Range object that includes all cells with values, formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
The "Is Nothing" test for ranges (also suggested by Barb) nicely avoids the
difficulty of distinguishing no cells from empty cells. Thanks. I prefer my sequential construction to doing all the combining at the end, because it makes it easier to add or remove conditions without rethinking the architecture. I take it that I did not overlook some easy way to directly construct the combined range without all the fuss? I'm glad that neither of you were confused by the typo of IsEmpty(Count) which was supposed to have been Not IsEmpty(area2) Sorry about that. Thanks again for the feedback, Jerry "Dave Peterson" wrote: I'd use 3 range variables and combine them into 1 big one. Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngComm As Range Dim RngTot As Range Set RngConst = Nothing Set RngForm = Nothing Set RngComm = Nothing Set RngTot = Nothing With ActiveSheet On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) Set RngComm = .Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 End With If RngConst Is Nothing Then If RngForm Is Nothing Then If RngComm Is Nothing Then 'do nothing Else Set RngTot = RngComm End If Else 'has formulas If RngComm Is Nothing Then Set RngTot = RngForm Else Set RngTot = Union(RngForm, RngComm) End If End If Else 'has constants If RngForm Is Nothing Then If RngComm Is Nothing Then Set RngTot = RngConst Else Set RngTot = Union(RngConst, RngComm) End If Else 'has formulas, too If RngComm Is Nothing Then Set RngTot = Union(RngConst, RngForm) Else Set RngTot = Union(RngConst, RngForm, RngComm) End If End If End If If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub ====== A long time ago, I saw a post by Tom Ogilvy that was pretty neat. Someone asked a similar question and his solution was to insert a new worksheet (or a worksheet in a new workbook) and use that to help. Something like: Option Explicit Sub testme2() Dim ActWks As Worksheet Dim TmpWks As Worksheet Dim Addr As String Dim RngTot As Range Set ActWks = ActiveSheet Set TmpWks = Workbooks.Add(1).Worksheets(1) Set RngTot = Nothing With ActWks On Error Resume Next Addr = "" Addr = .Cells.SpecialCells(xlCellTypeConstants).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeFormulas).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeComments).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = TmpWks.Cells.SpecialCells(xlCellTypeConstants).Add ress TmpWks.Parent.Close savechanges:=False If Addr = "" Then 'do noting Else Set RngTot = .Range(Addr) End If End With If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub I thought it was pretty slick. And if you decide to add more requirements, it's easier (I think) to modify. Jerry W. Lewis wrote: I need to construct a Range object that includes all cells with values, formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
I don't think you missed any easy way of doing this, but I don't think you
should be using isempty() to check what may be a multi-cell range. Checking isempty() with a single cell is ok. But that actually tells you if the cell is empty--not a problem when you're looking at constants or formulas, but could be a problem with cells with comments. Jerry W. Lewis wrote: The "Is Nothing" test for ranges (also suggested by Barb) nicely avoids the difficulty of distinguishing no cells from empty cells. Thanks. I prefer my sequential construction to doing all the combining at the end, because it makes it easier to add or remove conditions without rethinking the architecture. I take it that I did not overlook some easy way to directly construct the combined range without all the fuss? I'm glad that neither of you were confused by the typo of IsEmpty(Count) which was supposed to have been Not IsEmpty(area2) Sorry about that. Thanks again for the feedback, Jerry "Dave Peterson" wrote: I'd use 3 range variables and combine them into 1 big one. Option Explicit Sub testme() Dim RngConst As Range Dim RngForm As Range Dim RngComm As Range Dim RngTot As Range Set RngConst = Nothing Set RngForm = Nothing Set RngComm = Nothing Set RngTot = Nothing With ActiveSheet On Error Resume Next Set RngConst = .Cells.SpecialCells(xlCellTypeConstants) Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas) Set RngComm = .Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 End With If RngConst Is Nothing Then If RngForm Is Nothing Then If RngComm Is Nothing Then 'do nothing Else Set RngTot = RngComm End If Else 'has formulas If RngComm Is Nothing Then Set RngTot = RngForm Else Set RngTot = Union(RngForm, RngComm) End If End If Else 'has constants If RngForm Is Nothing Then If RngComm Is Nothing Then Set RngTot = RngConst Else Set RngTot = Union(RngConst, RngComm) End If Else 'has formulas, too If RngComm Is Nothing Then Set RngTot = Union(RngConst, RngForm) Else Set RngTot = Union(RngConst, RngForm, RngComm) End If End If End If If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub ====== A long time ago, I saw a post by Tom Ogilvy that was pretty neat. Someone asked a similar question and his solution was to insert a new worksheet (or a worksheet in a new workbook) and use that to help. Something like: Option Explicit Sub testme2() Dim ActWks As Worksheet Dim TmpWks As Worksheet Dim Addr As String Dim RngTot As Range Set ActWks = ActiveSheet Set TmpWks = Workbooks.Add(1).Worksheets(1) Set RngTot = Nothing With ActWks On Error Resume Next Addr = "" Addr = .Cells.SpecialCells(xlCellTypeConstants).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeFormulas).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = .Cells.SpecialCells(xlCellTypeComments).Address If Addr = "" Then 'skip it Else TmpWks.Range(Addr).Value = 1 End If Addr = "" Addr = TmpWks.Cells.SpecialCells(xlCellTypeConstants).Add ress TmpWks.Parent.Close savechanges:=False If Addr = "" Then 'do noting Else Set RngTot = .Range(Addr) End If End With If RngTot Is Nothing Then MsgBox "nothing found" Else MsgBox RngTot.Address End If End Sub I thought it was pretty slick. And if you decide to add more requirements, it's easier (I think) to modify. Jerry W. Lewis wrote: I need to construct a Range object that includes all cells with values, formulas, or comments. That seems surprisingly hard to do, because SpecialCells throws an error if no cells of the specified type are found, and Union fails if any of the component ranges are empty. The following code works, but it feels like I am working way to hard. Any suggestions? Jerry Sub tryit() ' make Range object containing all cells with values, formulas, or comments On Error Resume Next Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants ) If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) If IsEmpty(Count) Then Set area = Union(area, area2) ' Union() fails if either range is empty area2 = Empty End If If IsEmpty(area) Then Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) Else Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) If area2.Count 0 Then Set area = Union(area, area2) ' .Count is only reliable way to distinguish no cells from empty cells with comments End If area2 = 0 area2 = area.Count ' if area2 = 0, then no cells were found On Error GoTo 0: Err.Clear area.Select ' for easy verification that the snippet worked End Sub -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
Sorry if I was not clear; I agreed that IsEmpty() did not do what I wanted
while "Is Nothing" did; therefore "Is Nothing" is the approach I will be using. Thank you for pointing me in the right direction. Jerry "Dave Peterson" wrote: I don't think you missed any easy way of doing this, but I don't think you should be using isempty() to check what may be a multi-cell range. Checking isempty() with a single cell is ok. But that actually tells you if the cell is empty--not a problem when you're looking at constants or formulas, but could be a problem with cells with comments. Jerry W. Lewis wrote: The "Is Nothing" test for ranges (also suggested by Barb) nicely avoids the difficulty of distinguishing no cells from empty cells. Thanks. .... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union of possibly empty ranges
Oops. I did misunderstand.
But glad you have a solution that works. Jerry W. Lewis wrote: Sorry if I was not clear; I agreed that IsEmpty() did not do what I wanted while "Is Nothing" did; therefore "Is Nothing" is the approach I will be using. Thank you for pointing me in the right direction. Jerry "Dave Peterson" wrote: I don't think you missed any easy way of doing this, but I don't think you should be using isempty() to check what may be a multi-cell range. Checking isempty() with a single cell is ok. But that actually tells you if the cell is empty--not a problem when you're looking at constants or formulas, but could be a problem with cells with comments. Jerry W. Lewis wrote: The "Is Nothing" test for ranges (also suggested by Barb) nicely avoids the difficulty of distinguishing no cells from empty cells. Thanks. ... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
printing Union of Ranges | Excel Worksheet Functions | |||
Union of Ranges Failed | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
VBA union of two ranges | Excel Programming |