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)

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)

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)

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

Another way that may prove useful especially when deleting rows. Offset from
a1
=OFFSET(CSV!$A$1,0,0,COUNTA(CSV!$A:$A),13

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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)

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)