SaveAs Method adding a large number of comma-delimited blank lines
 

Hi all,


I am having a problem using the Save As Method using the
fileformat xlCSV (I have also tried xlcsvmsdos & xltextmsdos).

Here is a snippet of the code :

dim l1 as Long

With Worksheets("xyz")
l1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

[l1 returns the correct line count and a print preview also correctly
prints only those rows].


Worksheets("xyz").SaveAs Filename:= _
"C:\My Documents\xyz", FileFormat:=xlCSV

After this command, I get the correct data PLUS I would guess about a
1000 lines of data that looks like this :
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


Anyone seen anything like this, and, even better, is there a way to
prevent it happening ?

Thanks in advance,

Niall Heelan

SaveAs Method adding a large number of comma-delimited blank lines
 

How about adding this line of code to your macro

Rows((l1 + 1) & ":" & rows.Count).delete

" wrote:


Hi all,


I am having a problem using the Save As Method using the
fileformat xlCSV (I have also tried xlcsvmsdos & xltextmsdos).

Here is a snippet of the code :

dim l1 as Long

With Worksheets("xyz")
l1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

[l1 returns the correct line count and a print preview also correctly
prints only those rows].


Worksheets("xyz").SaveAs Filename:= _
"C:\My Documents\xyz", FileFormat:=xlCSV

After this command, I get the correct data PLUS I would guess about a
1000 lines of data that looks like this :
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


Anyone seen anything like this, and, even better, is there a way to
prevent it happening ?

Thanks in advance,

Niall Heelan

SaveAs Method adding a large number of comma-delimited blank lines
 

On Oct 19, 3:32 pm, Joel wrote:
How about adding this line of code to your macro

Rows((l1 + 1) & ":" & rows.Count).delete


Hi Joel,


Perfect.


Thanks again.

Niall
..