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

Try:

Range "A"

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

Range "B"

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

etc

HTH

"Guy Normandeau" wrote:

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

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.



"Toppers" wrote:

Try:

Range "A"

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

Range "B"

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

etc

HTH

"Guy Normandeau" wrote:

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

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

"Guy Normandeau" wrote:

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.



"Toppers" wrote:

Try:

Range "A"

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

Range "B"

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

etc

HTH

"Guy Normandeau" wrote:

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

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


"Toppers" wrote:

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

"Guy Normandeau" wrote:

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.



"Toppers" wrote:

Try:

Range "A"

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

Range "B"

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

etc

HTH

"Guy Normandeau" wrote:

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

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

=OFFSET(Sheet1!$A$1,0,0,MAX(MATCH(LOOKUP(99^99,She et1!$A:$A),Sheet1!$A:$A,0),MATCH(LOOKUP(99^99,Shee t1!$B:$B),Sheet1!$B:$B,0),MATCH(LOOKUP(99^99,Sheet 1!$C:$C),Sheet1!$C:$C,0)),1)

HTH

"Guy Normandeau" wrote:

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


"Toppers" wrote:

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

"Guy Normandeau" wrote:

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.



"Toppers" wrote:

Try:

Range "A"

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

Range "B"

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

etc

HTH

"Guy Normandeau" wrote:

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

Bonjour Normand

You can try this

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


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=564430

How would I use this in defining a named range?

"jetted" wrote:


Bonjour Normand

You can try this

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


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=564430

"Guy Normandeau" skrev i en
meddelelse ...
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.


--
Best regards
Leo Heuser

Followup to newsgroup only please.