Save As CSV saves empty rows

G

Garrett

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?
 
D

Dave Peterson

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?)
 
G

Garrett

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
 
D

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.)
 
Top