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(A pplication!$L:$L))

Any help would be greatly appreciated.

Thanks,

Sam

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
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"The Narcissist" wrote:

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(A pplication!$L:$L))

Any help would be greatly appreciated.

Thanks,

Sam

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?

--
Biff
Microsoft Excel MVP


"The Narcissist" wrote in message
...
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(A pplication!$L:$L))

Any help would be greatly appreciated.

Thanks,

Sam

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.



"The Narcissist" wrote:

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(A pplication!$L:$L))

Any help would be greatly appreciated.

Thanks,

Sam

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 elsewhe
=Application!$N$2:INDEX(Application!$N$2:$N$100,SU MPRODUCT(--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
---
"The Narcissist" wrote:
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(A pplication!$L:$L))