Dynamic range question

[Guest]

I've been using dynamic ranges for a while now and everything has been
working great until now. I found that in one of my ranges I have 10% blank
cells. Unformtunately, I don't have any columns that are completely filled
with data. Is there a function that I could use in the definition of named
range that would include the blanks?

A B C
1 B001 c001
2 A002 c002
3 B003
4 A004 B004
5 C005
6 A006 B006
7 A006 B007 C007
8 B008

When I use CountA for column A the function returns a value of 4, 6 for
column B and 4 for Column C. What I would like to return is 8 since there is
data in 8 rows.

Thanks in advande for your help.


Guy Normandeau

 

[jetted]

Bonjour Normand

You can try this

rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row

 

[Guest]

Try:

Range "A"

=OFFSET(Sheet1!$A1,0,0,MAX(COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$B:$B),COUNT(Sheet1!$C:$C)),1)

Range "B"

=OFFSET(Sheet1!$B1,0,0,MAX(COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$B:$B),COUNT(Sheet1!$C:$C)),1)

etc

HTH

 

[Guest]

I created named ranges tblA, tblB using the code exactly as stated below. I
also created tblC referencing column C and I get the following.

When selecting range tblA cells A1 through A6 are highlighted.
When selecting range tblB cells B1 through B6 are highlighted.
When selecting range tblC cells C1 through C6 are highlighted.

All ranges is missing the data in rows 7 and 8.

Unless I'm doing something wrong, it appears as if dynamic ranges are not
possible using the function CountA if you have blank values in your data.

 

[Guest]

How would I use this in defining a named range?

 

[Guest]

It works OK for me.

If I type tblA in the "name box" then cells A1 to A8 are highlighted and it
is the same for tblB and tblC i.e. rows 1 to 8 are highlighted for columns B
& C.

.... and you cannot use the "rowcount=...." suggestions unless you are using
VBA and this stops at the last non-blank entry in a column so still have to
determine which is the longest column.

Excel 2003

 

[Guest]

As per the microsoft help:

COUNTA

Counts the number of cells that are not empty and the values within the list
of arguments. Use COUNTA to count the number of cells that contain data in a
range or array.

Syntax

COUNTA(value1,value2,...)

Value1, value2, ... are 1 to 30 arguments representing the values you
want to count. In this case, a value is any type of information, including
empty text ("") but not including empty cells. If an argument is an array or
reference, empty cells within the array or reference are ignored. If you do
not need to count logical values, text, or error values, use the COUNT
function.

In My case, the blanks cells are empty cells therefore are not counted
therefore, the named range is short.

Guy

 

[Guest]

You initially stated blank cells, but given they are empty, try this:

=OFFSET(Sheet1!$A$1,0,0,MAX(MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A,0),MATCH(LOOKUP(99^99,Sheet1!$B:$B),Sheet1!$B:$B,0),MATCH(LOOKUP(99^99,Sheet1!$C:$C),Sheet1!$C:$C,0)),1)

HTH

 

[Guest]

....rewind! this will only work if the values in any column are unique and in
ascending order!! Back to the drawing board.

 

[Leo Heuser]

I've been using dynamic ranges for a while now and everything has been
working great until now. I found that in one of my ranges I have 10%
blank
cells. Unformtunately, I don't have any columns that are completely
filled
with data. Is there a function that I could use in the definition of
named
range that would include the blanks?

A B C
1 B001 c001
2 A002 c002
3 B003
4 A004 B004
5 C005
6 A006 B006
7 A006 B007 C007
8 B008

When I use CountA for column A the function returns a value of 4, 6 for
column B and 4 for Column C. What I would like to return is 8 since there
is
data in 8 rows.

Thanks in advande for your help.


Guy Normandeau

Hi Guy

If I have understood you correctly, here's one way:

Define the named range ColA with this formula:

=OFFSET($A$1,,,MAX(IF($A$1:$C$1000<>"",ROW($A$1:$C$1000))))

Similar for ColB and ColC

Make $C$1000 as large as you need it.

 

[Guest]

Also, this only works if you have at one numeric value in your column.

 

[Leo Heuser]

Didn't my answer from yesterday reach your NG server?

 

[Guest]

Sorry, I did not see your answer when I replied but it does exactly what I
need.

Thanks Leo!

 

[Leo Heuser]

Sorry, I did not see your answer when I replied but it does exactly what I
need.

Thanks Leo!

You're welcome, Guy, and thanks for the feedback

Leo Heuser