Dynamic range question

[Guy Normandeau]

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

[Toppers]

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

"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
>
>

[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),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
>
> "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
> >
> >

[Guy Normandeau]

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
>
>

[Toppers]

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),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
> >
> > "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
> > >
> > >

[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),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
> > >
> > > "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
> > > >
> > > >

[Toppers]

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

"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),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
> > > >
> > > > "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
> > > > >
> > > > >

[Toppers]

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

"Toppers" wrote:

> 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
>
> "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),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
> > > > >
> > > > > "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
> > > > > >
> > > > > >

[Leo Heuser]

"Guy Normandeau" <[email protected]> skrev i en
meddelelse news:[email protected]...
> 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.

[Guy Normandeau]

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

"Toppers" wrote:

> ...rewind! this will only work if the values in any column are unique and in
> ascending order!! Back to the drawing board.
>
> "Toppers" wrote:
>
> > 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
> >
> > "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),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
> > > > > >
> > > > > > "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
> > > > > > >
> > > > > > >

[Leo Heuser]

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

--
Best regards
Leo Heuser

Followup to newsgroup only please.

[Guy Normandeau]

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

Thanks Leo!


"Leo Heuser" wrote:

> Didn't my answer from yesterday reach your NG server?
>
> --
> Best regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
>
>

[Leo Heuser]

"Guy Normandeau" <[email protected]> skrev i en
meddelelse news:[email protected]...
> 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