Dynamic ranges - blank rows

[LUMPIA]

Hello,

I am brand new to the board "officially". I have browsed many, many times and found such helpful information. There are some real experts on this forum.

I am stuck getting this formula to work properly. My apologies if this is already addressed somewhere....

I want to create Dynamic Ranges that expand and contract with changing source data that I drive a series of pivots from (expanding rows and columns).

There may be instances where there is a blank cell in the row and I need to ensure the range sees any data appearing after that. I have tried to substitute my COUNTA with MATCH, but I can get the syntax correct.

The formula below works, but stops at blanks.

=OFFSET(DataA!$A$1,0,0,COUNTA(DataA!$A:$A),COUNTA(DataA!$1:$1))

(DataA is what I name the worksheet and range)

Any suggestions?

Regards,
Liz

 

[Jonmo1]

Welcome to the board...

Is your data TEXT or NUMERIC ?

To get the last NUMERIC Row, use
MATCH(9.99999999999999E+307,DataA!$A:$A)


To get the last TEXT Row, use
MATCH(REPT("Z",255),DataA!$A:$A)


So replace each of the counta's in your formula as required.

Hope that helps.

 

[LUMPIA]

WOW! Thanks for the insanely quick response.

My data could be either text or numeric. My file contains about 15 different tabs representing each Range. I then have 40+ pivot tables referencing them. I'm hoping to have a formula that will cover all variables. I can then pop it into any of my files with confidence and little or no maintenance.

Is there a way to account for text or numeric blanks in the same formula?

 

[Aladin Akyurek]

Define VSize as referring to:

If column A on DataA is numeric...

=MATCH(9.99999999999999E+307,DataA!$A:$A)-ROW(DataA!$A$2)+1

If column A on DataA consists of text-values...

=MATCH(REPT("z",255),DataA!$A:$A)-ROW(DataA!$A$2)+1

The foregoing definition does not include A1, part of the header row, but
if that is desired, simply adjust to suit.

Define HSize to refer to:

=MATCH(REPT("z",255),DataA!$1:$1)-COLUMN(DataA!$A$1)+1

We can now define Data (or DataA) as referring to:

=OFFSET(DataA!$A$2,0,0,VSize,HSize)

 

[Jonmo1]

Is there a way to account for text or numeric blanks in the same formula?

Use

=MATCH(2,1/($A$1:$A$65535<>""))

Note the row references,
In XL2003 and prior, you cannot use entire column refs in an array formula.
You can in XL2007 or higher, but not recommended.

 

[Haseeb Avarakkan]

Hello, Try this;

=MAX(LOOKUP(1E+100,CHOOSE({1,2},1,MATCH(1E+100,$A:$A))),LOOKUP(1E+100,CHOOSE({1,2},1,MATCH(REPT("Z",250),$A:$A))))

Is there a way to account for text or numeric blanks in the same formula?

 

[LUMPIA]

Use

=MATCH(2,1/($A$1:$A$65535<>""))

Note the row references,
In XL2003 and prior, you cannot use entire column refs in an array formula.
You can in XL2007 or higher, but not recommended.

jonmo1, I am unable to get this working properly. This is what i tried:

=OFFSET(DataA!$A$1,0,0, MATCH(2,1/($A$:$A$<>""))MATCH(2,1/($A$1:$A$65535<>""))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Also I am running XL2007 & XL2010 on different machines that I work on. Since the rows are greater than 65,535 from XL2003 - should I just increase the range? You recommended not referencing entire columns in XL2007, can you explain why? I also would prefer to stay away from arrays if possible.

Thanks for all your help
Liz

 

[LUMPIA]

Define VSize as referring to:

If column A on DataA is numeric...

=MATCH(9.99999999999999E+307,DataA!$A:$A)-ROW(DataA!$A$2)+1

If column A on DataA consists of text-values...

=MATCH(REPT("z",255),DataA!$A:$A)-ROW(DataA!$A$2)+1

The foregoing definition does not include A1, part of the header row, but
if that is desired, simply adjust to suit.

Define HSize to refer to:

=MATCH(REPT("z",255),DataA!$1:$1)-COLUMN(DataA!$A$1)+1

We can now define Data (or DataA) as referring to:

=OFFSET(DataA!$A$2,0,0,VSize,HSize)

Aladin,

Thank you for spending time to help me with this problem. I am trying to apply your formula, but not having much success. I am not as advanced as you- so please bear with me... When you say "Define Hsize" are you refering to me creating a UDF(User defined Funcition)? Can this handle text or numeric in the same formula?

Thank you,
Liz

 

[Jonmo1]

Try

=OFFSET(DataA!$A$1,0,0, MATCH(2,1/(DataA!$A:$A<>"")),MATCH(2,1/(DataA!$1:$1<>"")))<?xml:namespace prefix = o /><o></o>

 

[Aladin Akyurek]

Aladin,

Thank you for spending time to help me with this problem. I am trying to apply your formula, but not having much success. I am not as advanced as you- so please bear with me... When you say "Define Hsize" are you refering to me creating a UDF(User defined Funcition)? Can this handle text or numeric in the same formula?

Thank you,
Liz

A name is defined by means of Insert | Name | Define (on all versions
prior to 2007) or by means of Formulas | Name Manager, so there is no need for VBA.