Home 
Search 
Today's Posts 
#1




Defined range difficulty
The defined range below extends the range beyond the cells where the data
ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#2




You obviously have data in C1:C77 thus it will be counted, you can use
=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) Regards Peo Sjoblom "Pat" wrote: The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#3




Quote:
Beyond that I am not sure what your question is... "Pat" wrote in message ... The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#4




You obviously have data in C1:C77 thus it will be counted, you can use
Yes you are correct. This has now eliminated many empty cells. I say many because where there is an empty cell with a formula the range includes these. The data finishes at C807 but the formula continues on to C1000. Can the defined range be made to ignore a formula range on a sheet? Pat "Peo Sjoblom" wrote in message ... You obviously have data in C1:C77 thus it will be counted, you can use =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) Regards Peo Sjoblom "Pat" wrote: The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#5




Hi Get the last used row in your range using something like the line below starting with LastRow. The "c65536" should be a column in your range that will always have data in it. Change the c to the appropriate col letter. LastRow = ActiveSheet.Range("c65536").End(xlUp).Offset(0, 0).Row Assuming LastRow above returns row 30 , the code below redefines DataRange to be row 5 col B to row 30 column i or B5:I30 ' resize the named range datarange ActiveWorkbook.Names.Add Name:="Datarange", RefersToR1C1:="=BalanceSheet!" & "r" & 5 & "c" & 2 & ":" & "r" & LastRow & "c" & 9 If you are showing the data on a userform in a listbox or combobox, set its Rowsource property to DataRange and then use the code below to show only the data with no blank lines at the bottom in the listbox. ' the following code re dimensions the Listbox1 rowsource so ' no blank lines are shown in the Listbox sstring = Mainform.ListBox1.RowSource Mainform.ListBox1.RowSource = sstring HTH Ken 
#6




You can use this instead of counta
SUMPRODUCT((C77:C65536<"")) Regards, Peo Sjoblom "Pat" wrote: You obviously have data in C1:C77 thus it will be counted, you can use Yes you are correct. This has now eliminated many empty cells. I say many because where there is an empty cell with a formula the range includes these. The data finishes at C807 but the formula continues on to C1000. Can the defined range be made to ignore a formula range on a sheet? Pat "Peo Sjoblom" wrote in message ... You obviously have data in C1:C77 thus it will be counted, you can use =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) Regards Peo Sjoblom "Pat" wrote: The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#7




Did you mean to enter it like this:
=OFFSET(Centre!$C$77,0,0,SUMPRODUCT((C77:C65536<"")) or to enter like this: =OFFSET(Centre!$C$77,0,0,SUMPRODUCT(Centre!(C77:C6 5536<"")) Either way I have not been successful. Pat "Peo Sjoblom" wrote in message ... You can use this instead of counta SUMPRODUCT((C77:C65536<"")) Regards, Peo Sjoblom "Pat" wrote: You obviously have data in C1:C77 thus it will be counted, you can use Yes you are correct. This has now eliminated many empty cells. I say many because where there is an empty cell with a formula the range includes these. The data finishes at C807 but the formula continues on to C1000. Can the defined range be made to ignore a formula range on a sheet? Pat "Peo Sjoblom" wrote in message ... You obviously have data in C1:C77 thus it will be counted, you can use =OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1)) Regards Peo Sjoblom "Pat" wrote: The defined range below extends the range beyond the cells where the data ends. The start of the range is Centre!$C$77 the end of the range is C1054. The last cell containing data is C807. The data in C is the result of a formula and is in the range C77:C1000 =OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1) Anyone familiar with all of this? Thanks if you can help. Pat 
#8




I am unclear how to use your approach, is this to be used in a userform. I
have little experience with userforms. Pat "Ken Macksey" wrote in message ... Hi Get the last used row in your range using something like the line below starting with LastRow. The "c65536" should be a column in your range that will always have data in it. Change the c to the appropriate col letter. LastRow = ActiveSheet.Range("c65536").End(xlUp).Offset(0, 0).Row Assuming LastRow above returns row 30 , the code below redefines DataRange to be row 5 col B to row 30 column i or B5:I30 ' resize the named range datarange ActiveWorkbook.Names.Add Name:="Datarange", RefersToR1C1:="=BalanceSheet!" & "r" & 5 & "c" & 2 & ":" & "r" & LastRow & "c" & 9 If you are showing the data on a userform in a listbox or combobox, set its Rowsource property to DataRange and then use the code below to show only the data with no blank lines at the bottom in the listbox. ' the following code re dimensions the Listbox1 rowsource so ' no blank lines are shown in the Listbox sstring = Mainform.ListBox1.RowSource Mainform.ListBox1.RowSource = sstring HTH Ken 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Passing a range name as an argument to the Index Function  Excel Discussion (Misc queries)  
Construct a range in VB  Excel Discussion (Misc queries)  
How do I sum a range after 2 different conditions are met (2 colu.  Excel Discussion (Misc queries)  
named range refers to: in a chart  Excel Discussion (Misc queries)  
HTML_Control Range name  Excel Discussion (Misc queries) 