|
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
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 |
Dynamic range question
....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,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 |
Dynamic range question
"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. |
Dynamic range question
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,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 |
Dynamic range question
Didn't my answer from yesterday reach your NG server?
-- Best regards Leo Heuser Followup to newsgroup only please. |
Dynamic range question
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. |
Dynamic range question
"Guy Normandeau" skrev i en
meddelelse ... 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 |
| All times are GMT +1. The time now is 05:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com