Automatic CSV saving pains - unwanted rows and columns

[Guest]

Hi, I'm currently using a macro to prompt a user to save a sheet as a CSV
file. I already have code in place that hides all the unwanted cells and
whilst the CSV file saves perfectly when done manually, looking like this:

asf,adsff,fsdf
gtre,ffdf,etth

when I run the appropriate part of the code, which is this:

Do
fname = Application.GetSaveAsFilename(fileFilter:="CSV files (*.csv), *.csv")
Loop Until fname <> False
Workbooks(newreport).SaveAs Filename:=fname, FileFormat:=xlCSV,_
CreateBackup:=False, Local:=True

Workbooks(fname).Saved = True
Workbooks(fname).Close

it produces a CSV file that looks like this:

asaf,dsdf,fadsf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
fdsd,fdsf,fdsf,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

I just can't figure out why this is happening, even if I go and actually
record the code there appears to be no differences.. Can any one help?

Thanks in advance!

 

[Dave Peterson]

When you hit ctrl-end, does this take you further down and to the right than
what you'd expect.

If you use some of the techniques at Debra Dalgleish's site to reset that last
used cell, does your code work ok?

http://contextures.com/xlfaqApp.html#Unused

Other options would include copying just the data you want to a new worksheet in
a new workbook and saving from there.

Or writing your own code to extract the comma separated values.

You can get a leg up on that code by reviewing these:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html