Question about dynamic ranges

T

The Narcissist

Hi All,

I'm using some dynamic ranges on an excel file I'm working on. One range has
a problem though. The column contains fomulas and the default result is a
blank. When I reference this range, it also takes in the blank values. This
is impacting some other fomulas on the file. Is there a way I can modify the
range to exclude blanks?

This is how the range is defined.

=Application!$L$2:INDEX(Application!$L:$L,COUNTA(Application!$L:$L))

Any help would be greatly appreciated.

Thanks,

Sam
 
B

Barb Reinhardt

One way (programmatically)

Option Explicit

Sub test()
Dim myRange
Dim myNewRange As Range
Dim r As Range

'Set myRange = ... as you define it

For Each r In myRange
If Not IsEmpty(r) Then
If myNewRange Is Nothing Then
Set myNewRange = r
Else
Set myNewRange = Union(myNewRange, r)
End If

End If

Next r

If myNewRange Is Nothing Then
MsgBox ("All cells are empty")
Exit Sub
End If

Set myRange = myNewRange

End Sub



If you are using it in an equation, you could do something like this

=average(if(ISBLANK(MYRANGE)=FALSE,MYRANGE))

Commit with CTRL SHIFT ENTER
 
T

T. Valko

What version of Excel are you using?

The cells that contain formula blanks, are they at one end of the range or
at random locations within the range?
 
J

Jonathan Cooper

Will this work?

=OFFSET(Application!$L$2,,,COUNT(Application!$A:$A),COUNTA(application!$1:$1))

if you use 'COUNT' rather than COUNTA, it will ignore blanks, but that means
it will not include your column header, so that is why I started at L2.

The last part of the formula determines how many columns are included in
your dynamic range. if you only want 1 column, replace
'counta(applicaiton!!$1:$1) with the number 1.
 
M

Max

One construct which will deliver it here ..

In Application,
Data/Formulas is running in L2 down,
with possibility of blank intervening cells or cells containing formula
blanks: ""
In M2: =IF(L2="","",ROW())
In N2: =INDEX(L:L,SMALL(M:M,ROWS($1:1)))
Copy M2:N2 down to cover the max extent expected in col L

Then you could use this amendment of your posted expression (pointing now to
col N) as the dynamic range for your DVs elsewhere:
=Application!$N$2:INDEX(Application!$N$2:$N$100,SUMPRODUCT(--ISNUMBER(Application!$N$2:$N$100)))

The above dynamic range will return the required compacted list devoid of
intervening blanks in the DVs
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
Top