Defining a discontiguous Range object

[Charley Kyd]

I have a spreadsheet column that could include empty cells, zero values,
strings, and non-zero values. I would like to define a Range object that
references only the non-zero values. I'd prefer not to loop.

Ideally, the method also would allow me to define a Range based on other
criteria, like cells with values greater than 100, or whatever.

This is easy to do in an array formula in a spreadsheet, with something
like:
=Sum(If(Foo>100,Foo,0))

I've been playing around with FormulaArray. But I don't see a clean way to
define a range object based on this property.

Does anyone have any ideas?

Thanks.

Charley

 

[Jim Cone]

Charley,

Take a look at the "SpecialCells" method in ExcelVB help.
It does what you want, except for picking out particular values.

Regards,
Jim Cone
San Francisco, CA

I have a spreadsheet column that could include empty cells, zero values,
strings, and non-zero values. I would like to define a Range object that
references only the non-zero values. I'd prefer not to loop.
Ideally, the method also would allow me to define a Range based on other
criteria, like cells with values greater than 100, or whatever.
This is easy to do in an array formula in a spreadsheet, with something
like: =Sum(If(Foo>100,Foo,0))
I've been playing around with FormulaArray. But I don't see a clean way to

define a range object based on this property.

 

[Charley Kyd]

Jim,

Unless I'm missing something, I don't think SpecialCells will do what I
want, without looping.

If someone didn't offer a better solution, I had thought about looping
through the data once, hiding each row that I don't want in my Range object,
and then using SpecialCells to return the discontiguous range of the
unhidden cells that I do want. That solution would be rather fast to code
and fast to execute. But I was hoping for something more direct.

Any other ideas?

Charley

 

[Jim Cone]

Charley,

Loops are a very useful tool and easy to write.
If written properly they will execute very fast.
With that said, code similar to the following will reduce the
searching/looping significantly...
'----------------------------------------------------------------
Set FirstRng = MyColumn.SpecialCells(xlCellTypeFormulas)
Set SecondRng = MyColumn.SpecialCells(xlCellTypeConstants)
'Union method generates an error if a range is nothing
If FirstRng Is Nothing Then
Set FirstRng = SecondRng
ElseIf SecondRng Is Nothing Then
Set SecondRng = FirstRng
End If
Set FinalRange = Application.Union(FirstRng, SecondRng)
'---------------------------------------------------------------
Now run your loop thru "FinalRange"

Regards,
Jim Cone
San Francisco, CA
====================================

Jim,

Unless I'm missing something, I don't think SpecialCells will do what I
want, without looping.

If someone didn't offer a better solution, I had thought about looping
through the data once, hiding each row that I don't want in my Range object,
and then using SpecialCells to return the discontiguous range of the
unhidden cells that I do want. That solution would be rather fast to code
and fast to execute. But I was hoping for something more direct.

Any other ideas?

Charley

- snip -

 

[Rob van Gelder]

Sub testit()
Dim rngSource As Range, rngFilter As Range, rng As Range, dblMySum As
Double

Set rngSource = Range("A1:A1000")

For Each rng In rngSource
If IsNumeric(rng.Value) And rng.Value >= 500 Then
If rngFilter Is Nothing Then
Set rngFilter = rng
Else
Set rngFilter = Union(rngFilter, rng)
End If
End If
Next

rngFilter.Select
For Each rng In rngFilter: dblMySum = dblMySum + rng.Value: Next
MsgBox dblMySum
End Sub

 

[Charley Kyd]

Thanks, Jim.

Charley

Charley,

Loops are a very useful tool and easy to write.
If written properly they will execute very fast.
With that said, code similar to the following will reduce the
searching/looping significantly...
'----------------------------------------------------------------
Set FirstRng = MyColumn.SpecialCells(xlCellTypeFormulas)
Set SecondRng = MyColumn.SpecialCells(xlCellTypeConstants)
'Union method generates an error if a range is nothing
If FirstRng Is Nothing Then
Set FirstRng = SecondRng
ElseIf SecondRng Is Nothing Then
Set SecondRng = FirstRng
End If
Set FinalRange = Application.Union(FirstRng, SecondRng)
'---------------------------------------------------------------
Now run your loop thru "FinalRange"

Regards,
Jim Cone
San Francisco, CA
====================================


- snip -

 

[Wei-Dong Xu [MSFT]]

Hi Charley,

Thank you for posting in MSDN managed newsgroup!

From my understanding to this issue, you are going to find one quick way to filter some values from the excel worksheet.

If all the values in the cells are not sorted, you will need to use Loop to pick up the values you want. So far as I know in this scenario, the quickest
way for you is to use SpecialCell method as Jim has suggested. For the usage of SpecialCell mentioned in your reply, I agree with that you should
hide some cells so that SpecialCell can filter the value for you.

Furthermore, if all the values has been sorted, it will be simple for you to obtain the specified value. I'd suggest you can sort the values during the
creation of the worksheet and then it will be very fast for you to locate these special values.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Charley Kyd]

Because nobody could suggest a non-looping way to grab a discontiguous
range, the looping version that I came up with is shown below. Because it
hides unwanted cells in blocks, and because Rows.Hidden = True is a fast
operation, this probably runs about as quickly as anything.

Charley

''==========================================================================
=====
'' Program: NonZeroRange
'' Desc: Returns a discontiguous range of all cells with a non-zero
value
'' found in a single-column range
'' Called by:
'' Call: NonZeroRange(rngData)
'' Arguments: rngData--The source range
'' Comments: This could be modified to use more sophisticated filters.
''
Changes---------------------------------------------------------------------
-
'' Date Programmer Change
'' 12/25/03 Charley Kyd Written
''==========================================================================
=====
Private Function NonZeroRange(rngData As Range) As Range
Dim rngCur As Range, rngStart As Range, bDoingZeros As Boolean
Dim gVal As Single

bDoingZeros = False

''Make sure all rows begin as unhidden
rngData.Rows.Hidden = False

For Each rngCur In rngData
''Trap error values. (Note: Using IIf doesn't work because it
''calculates both results, generating an error value.)
If IsError(rngCur) Then gVal = 0 Else gVal = Val(rngCur)

''If this is an item to hide...
If gVal = 0 Then
''If this is the first zero found in a block
If Not bDoingZeros Then
bDoingZeros = True
Set rngStart = rngCur
End If

''If this is a non-zero value
Else
''If we're done with a block of zeros...
If bDoingZeros Then
''Hide the current block, ending with the previous cell
Range(rngStart, rngCur.Offset(-1, 0)).Rows.Hidden = True
bDoingZeros = False
End If
End If
Next rngCur

''If the range ends with a zero...
If bDoingZeros Then
Range(rngStart,
rngData.SpecialCells(xlCellTypeLastCell)).Rows.Hidden = True
End If

''Define the range of searched-for values
Set NonZeroRange = rngData.SpecialCells(xlCellTypeVisible)

''Unhide the range
rngData.Rows.Hidden = False
End Function

 

[Dave Peterson]

Tom Ogilvy posted a pretty neat solution to a similar question that created a
new temporary worksheet, copied values there, and then manipulated some stuff on
that temporary worksheet.

Here's a version of that routine:

Option Explicit
Function NumericNonZeroRng(rng As Range) As Range

Dim wks As Worksheet
Dim myArea As Range
Dim myNewRng As Range

Set wks = Worksheets.Add

With wks
.Range(rng.Address).Value = rng.Value

On Error Resume Next
'get rid of text cells
.Cells.SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
'get rid of zero cells
.UsedRange.Replace what:=0, replacement:="", lookat:=xlWhole
Set myNewRng = Nothing
Set myNewRng = .Cells.SpecialCells(xlCellTypeConstants)

On Error GoTo 0
End With

If myNewRng Is Nothing Then
Set NumericNonZeroRng = Nothing
Else
Set NumericNonZeroRng = rng.Parent.Range(myNewRng.Address)
End If

Application.DisplayAlerts = False
wks.Delete
Application.DisplayAlerts = True

End Function

And to test it.

Sub testme01()

Dim myRng As Range
Dim myNonZeroRng As Range

Application.ScreenUpdating = False

With ActiveSheet
Set myRng = ActiveSheet.Range("a1").CurrentRegion
Set myNonZeroRng = NumericNonZeroRng(myRng)

If myNonZeroRng Is Nothing Then
MsgBox "none found"
Else
myNonZeroRng.Select 'for example
End If
End With

Application.ScreenUpdating = True

End Sub

The bad thing is that I don't see a way to modify it to keep cells based on a
criterion like > 100 (without looping).

Maybe inserting a new row, applying a filter based the opposite of your
criteria, and clearing the visible cells, then doing the next column (and so
forth).

 

[Charley Kyd]

Dave,

Thanks for the idea. But as you say, it doesn't allow for non-zero
filtering. It doesn't deal with possible error values in the range. And
because of past bugs and performance problems, I tend to shy away from using
temporary workbooks or worksheets.

I'm still looking for a more powerful way to define a range object.

Charley

 

[Tushar Mehta]

I'm curious. How is this better than Rob van Gelder's solution? Or
for that matter Dave Peterson's suggestion, which doesn't modify the
user's selection of visible / hidden rows?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

What's wrong with looping?

- Jon

 

[Charley Kyd]

Tushar,

I haven't tested Rob's solution; I read it after I had written my own.

But because it requires two loops, and because it performs a Union for each
cell that passes the filter, it feels like it would be slower than mine.

The key to my approach was that no action was necessary for each cell within
a contiguous group of cells.

But maybe I'm wrong; I often am.

Charley

 

[Charley Kyd]

Jon,

What's wrong with looping? Speed. Because the user will work interactively
with the data, I'm looking for an instantaneous response. I was assuming
that a non-looping solution would tend to be faster than a looping solution.

I should have emphasized *speed* in my original post.

Charley

 

[Rob van Gelder]

Charley,

If you want speed, then I recommend you find a way to measure it.

Here is a Microsoft KB article which will assist:
HOWTO: Use QueryPerformanceCounter to Time Code
http://support.microsoft.com/default.aspx?scid=kb;[LN];172338

Rob

 

[Charley Kyd]

Thanks, Rob. I hadn't seen that article before.

For years, I've relied on the Timer function in a nested loop. I like using
Timer because it takes less than a minute to write a performance-testing
program from scratch after the competing routines have been written.

To illustrate, if I want to compare the performance of two approaches, I'll
loop through one approach for, say, ten thousand loops. I'll add zeros to
the loop counter until the total time is about ten seconds. Then I'll loop
through the other approach for the same number of passes. Comparing the
number of seconds returned by Timer for each of the two tests gives me an
adequate guide.

Although the article says that Timer only has a one-second resolution, it
appears to report reliable times to at least three decimal places. Even that
precision isn't needed, however, because one approach tends to be several
times faster than the other. If two competing approaches take about the same
time to execute many thousands of passes, then I'll choose the alternative
that's shortest and easiest to understand.

There's a lot to be said for the KISS philosophy of programming.

All the best,

Charley

Charley,

If you want speed, then I recommend you find a way to measure it.

Here is a Microsoft KB article which will assist:
HOWTO: Use QueryPerformanceCounter to Time Code
http://support.microsoft.com/default.aspx?scid=kb;[LN];172338

Rob

Jon,

What's wrong with looping? Speed. Because the user will work interactively
with the data, I'm looking for an instantaneous response. I was assuming
that a non-looping solution would tend to be faster than a looping solution.

I should have emphasized *speed* in my original post.

Charley


Because
it

''==========================================================================Changes---------------------------------------------------------------------''==========================================================================