Home 
Search 
Today's Posts 
#1




I need to determine a cell range based on a blank cell
Debra Dalgleish wrote in message ...
You could use a dynamic formula to define the range in Excel. It will automatically adjust to include only the filled rows. There are some instructions he http://www.contextures.com/xlNames01.html#Dynamic That's not working for me. I'm getting a #VALUE! error. I realized I should give a little more information. I'm using Windows 2000, Excel XP and the VB program is created with VB 6. Here is what my table will look like: Phone number Reason Code Volume 8005552222 Abandon Call 6 Misc. Text 10 More Misc. Text 101 8005551111 Abandon Call 305 Text Text Text 3 More Text 4 For each report VB will dump the data into my Spreadsheet which may consist of 3 rows or 30 rows. When VB copies and pastes this table from excel I want it to know where the table should stop. VB does know to look for the Name "TableRange1" which is usually an absolute value (for example, A3:C24)but in this case I need to create it dynamically with each report. I hope that makes things clearer. I would appreciate any help anybody can give me. Thanks, Katrina Haggerty 
#2




I need to determine a cell range based on a blank cell
Using your example, the formula to define TableRange1 would be:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),3) This will create a range with rows equal to the number of items in column B, and 3 columns. Katrina wrote: Debra Dalgleish wrote in message ... You could use a dynamic formula to define the range in Excel. It will automatically adjust to include only the filled rows. There are some instructions he http://www.contextures.com/xlNames01.html#Dynamic That's not working for me. I'm getting a #VALUE! error. I realized I should give a little more information. I'm using Windows 2000, Excel XP and the VB program is created with VB 6. Here is what my table will look like: Phone number Reason Code Volume 8005552222 Abandon Call 6 Misc. Text 10 More Misc. Text 101 8005551111 Abandon Call 305 Text Text Text 3 More Text 4 For each report VB will dump the data into my Spreadsheet which may consist of 3 rows or 30 rows. When VB copies and pastes this table from excel I want it to know where the table should stop. VB does know to look for the Name "TableRange1" which is usually an absolute value (for example, A3:C24)but in this case I need to create it dynamically with each report. I hope that makes things clearer. I would appreciate any help anybody can give me. Thanks, Katrina Haggerty  Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
use active cell to determine range  Excel Discussion (Misc queries)  
How can I make a blank cell in a formula cell with a range of cell  Excel Discussion (Misc queries)  
Determine last blank cell in row?  Excel Worksheet Functions  
Determine next Saturday in calendar based on date in a cell  New Users to Excel  
Function to determine if any cell in a range is contained in a given cell  Excel Worksheet Functions 