|
Dynamic range issue
I'm using the following to set a dynamic range in an Excel 2003 workbook that
I use to determine records to be exported to a CSV file. My formula is setting the dynamic range with one extra (blank) row that results in my CSV file having a row of commas at the end of the file. That's causing all kinds of problems. Any help will be greatly appreciated. Dynamic range formula =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13) |
Dynamic range issue
Try to decrease No of rows:
=OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A)-1,13) Regards, Stefi €˛Ken Warthen€¯ ezt Ć*rta: I'm using the following to set a dynamic range in an Excel 2003 workbook that I use to determine records to be exported to a CSV file. My formula is setting the dynamic range with one extra (blank) row that results in my CSV file having a row of commas at the end of the file. That's causing all kinds of problems. Any help will be greatly appreciated. Dynamic range formula =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13) |
Dynamic range issue
Because you are starting at a2 and counting all of a, simply subtract one
=OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13 =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A)-1,13 Don Guillett Microsoft MVP Excel SalesAid Software "Ken Warthen" wrote in message ... I'm using the following to set a dynamic range in an Excel 2003 workbook that I use to determine records to be exported to a CSV file. My formula is setting the dynamic range with one extra (blank) row that results in my CSV file having a row of commas at the end of the file. That's causing all kinds of problems. Any help will be greatly appreciated. Dynamic range formula =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13) |
Dynamic range issue
You're including A1 in your cound, but you're offsetting from A2.
You could use Stefi's adjusted formula if you know that A1 always has something in it: =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A)-1,13) Or you could adjust the range to avoid A1: =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A2:$A65536)-1,13) or subtract the number of cells (just 1 in your example) that have something in them: =OFFSET(csv!$A$2,0,0,COUNTA(csv!$A:$A)-COUNTA(csv!$A$1),13) This might be more useful if you had 10 cells that may (or may not) have stuff in them: =OFFSET(csv!$A$11,0,0,COUNTA(csv!$A:$A)-COUNTA(csv!$A$1:$a$10),13) Ken Warthen wrote: I'm using the following to set a dynamic range in an Excel 2003 workbook that I use to determine records to be exported to a CSV file. My formula is setting the dynamic range with one extra (blank) row that results in my CSV file having a row of commas at the end of the file. That's causing all kinds of problems. Any help will be greatly appreciated. Dynamic range formula =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13) -- Dave Peterson |
Dynamic range issue
Thanks (Don and Stefi) for the help.
"Don Guillett" wrote: Because you are starting at a2 and counting all of a, simply subtract one =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13 =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A)-1,13 Don Guillett Microsoft MVP Excel SalesAid Software "Ken Warthen" wrote in message ... I'm using the following to set a dynamic range in an Excel 2003 workbook that I use to determine records to be exported to a CSV file. My formula is setting the dynamic range with one extra (blank) row that results in my CSV file having a row of commas at the end of the file. That's causing all kinds of problems. Any help will be greatly appreciated. Dynamic range formula =OFFSET(CSV!$A$2,0,0,COUNTA(CSV!$A:$A),13) |
| All times are GMT +1. The time now is 06:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com