Save As CSV saves empty rows
 

I have an XLS worksheet that copies cells from a different worksheet in the
same workbook using the following function "=T(UPPER(<other worksheet
name!<other worksheet cell)). The formula is copied throughout the
worksheet using relative cell reference. When I save the worksheet as a csv
file all of the records that have no data are also saved. What I end up with
in the csv file looks something like this:
data,data,data,data,
,,,,

How do I tell Excel to not save the empty rows?

But those aren't empty cells to excel. They contain formulas that evaluate to
"".

I think I'd copy the non-blank cells to a different worksheet and save from
there.

(But lots of times, this kind of stuff doesn't cause any damage to the importing
program. Are you sure it matters?)

Garrett wrote:

I have an XLS worksheet that copies cells from a different worksheet in the
same workbook using the following function "=T(UPPER(<other worksheet
name!<other worksheet cell)). The formula is copied throughout the
worksheet using relative cell reference. When I save the worksheet as a csv
file all of the records that have no data are also saved. What I end up with
in the csv file looks something like this:
data,data,data,data,
,,,,

How do I tell Excel to not save the empty rows?

--

Dave Peterson

Dave,

Thanks for your reply. Yeah, I tried and the import program I'm using chokes
pretty hard on the bogus records. I have written a macro in VB that removes
the blank rows and writes the remaining rows to a CSV file. The other option
would be to use a function that clears the cell if the T function returns
"", although I've searched the function list and can't find a function that
will clear the contents of a cell.

Again, thanks for your reply, I was begining to think there are more
questions on this newsgroup than answers.

Garrett

"Dave Peterson" wrote in message
...
But those aren't empty cells to excel. They contain formulas that
evaluate to
"".

I think I'd copy the non-blank cells to a different worksheet and save
from
there.

(But lots of times, this kind of stuff doesn't cause any damage to the
importing
program. Are you sure it matters?)

Garrett wrote:

I have an XLS worksheet that copies cells from a different worksheet in
the
same workbook using the following function "=T(UPPER(<other worksheet
name!<other worksheet cell)). The formula is copied throughout the
worksheet using relative cell reference. When I save the worksheet as a
csv
file all of the records that have no data are also saved. What I end up
with
in the csv file looks something like this:
data,data,data,data,
,,,,

How do I tell Excel to not save the empty rows?

--

Dave Peterson

If I really want empty cells, I'll do a little extra work.

I'll change my formula to return a #n/a.

=if(t(sheet2!a1)="",na(),t(sheet2!a1))

I'm not sure why you're using the =t() function (unless to stop the 0's being
returned from empty cells???).

=if(sheet2!a1="",na(),sheet2!a1)

Then copy|paste special|values
then
Edit|replace
what: #n/a
with: (leave empty)
replace all

But I think you'll still have to reset that last used cell of that worksheet.
http://www.contextures.com/xlfaqApp.html#Unused

to stop the new blanks from being written.

(If I were you, I'd continue using the macro that you have that's working.)


Garrett wrote:

Dave,

Thanks for your reply. Yeah, I tried and the import program I'm using chokes
pretty hard on the bogus records. I have written a macro in VB that removes
the blank rows and writes the remaining rows to a CSV file. The other option
would be to use a function that clears the cell if the T function returns
"", although I've searched the function list and can't find a function that
will clear the contents of a cell.

Again, thanks for your reply, I was begining to think there are more
questions on this newsgroup than answers.

Garrett

"Dave Peterson" wrote in message
...
But those aren't empty cells to excel. They contain formulas that
evaluate to
"".

I think I'd copy the non-blank cells to a different worksheet and save
from
there.

(But lots of times, this kind of stuff doesn't cause any damage to the
importing
program. Are you sure it matters?)

Garrett wrote:

I have an XLS worksheet that copies cells from a different worksheet in
the
same workbook using the following function "=T(UPPER(<other worksheet
name!<other worksheet cell)). The formula is copied throughout the
worksheet using relative cell reference. When I save the worksheet as a
csv
file all of the records that have no data are also saved. What I end up
with
in the csv file looks something like this:
data,data,data,data,
,,,,

How do I tell Excel to not save the empty rows?

--

Dave Peterson

--

Dave Peterson