Saving in csv file after removing unused cells

[Guest]

In one worksheet, I filtered out bunch of calculated data from several other
worksheet. Altough cell contents are "" after the first 10 row, when I try to
save file as csv or text, I end up having a big file with 1000s of lines with
comma sepateded blank values. Is there any way, I can eliminate this using
macro or visual basic.

Best Regards,

 

[Bill Renaud]

If you have done lots of editing, running of macros, etc. on the active
worksheet, then its UsedRange property will still include a lot of "empty"
cells that will be eliminated after the workbook is saved.

Try copying only the cells that still have data that you want to export to
the CSV file to a new worksheet in a new workbook, then saving.

 

[Guest]

Why do you edit the file with notepad and delete the blank lines. If it is
only one worksheet it doesn't pay to write code to fix a one time problem.

 

[Guest]

thank you for your quick reply. Let me explain the problem more clearly.

There are several worksheets which gets filtered out bunch of calculated
data from several other. Basicly each worksheet gets data from main data
worksheet with certain categories. (such as Product Name and Date Range).

Then a macro runs and selects each product worksheets and saves as comma
separated "product-name-mmddyy.txt" Since each worksheet pages covers 1400
rows, each row has formula and results of those formula mostly are ' "" '.
Only the first 10-100 row has values.

Saving each pages manually and then deleting unnecessary part of data is
time consuming especially when I am trying to do this every day.

So What I want to do is
either, selecting the last result value row number which is different than '
"" ' and selecting this range and saving this range as a csv file using VB.

or

deleting the area which results are "", then save the files as csv using VB

or some other way that currently I can not think of.

I really appreciate any helps. Thanks



Best Regards

 

[Guest]

Does this simple code help?

Sub removequotes()

Cells.Replace _
What:=Chr(34) & Chr(34), Replacement:="", lookat:=xlWhole
End Sub

 

[Guest]

Thank you for the code. Altough it was useful but it did not work for me. I
can send a simplified file that explain the situation. I am not sure, if I
can attach a file here. Let me explain litle more in detail.

Sheet1 : I have two bottons for a macro.

Sub Killfile()
Dim MyFile As String
On Error Resume Next 'On hitting errors, code resumes next code
MyFile = "C:\teama\data\teamA.txt"
Kill MyFile

On Error Resume Next 'On hitting errors, code resumes next code
MyFile = "C:\teamb\data\teamB.txt"
Kill MyFile

End Sub

Sub SaveAsText()

' Save Open files as csv in MS-DOS format
Sheets("teamA").Select
ChDir "C:\teama\data"
ActiveWorkbook.SaveAs Filename:= _
"C:\teama\data\teamA.txt", FileFormat _
:=xlCSV, CreateBackup:=False


' Save Open files as csv in MS-DOS format
Sheets("teamB").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\teama\data\teamB.txt", FileFormat _
:=xlCSV, CreateBackup:=False
End Sub




in "data" worksheet, I have following fields and data

product Rq date Sale Person Sale Date Price Sales ID Commission Data2 Data3
-


In C-teamA and C-teamB, certain criteria are selected and filetered data
from "data worksheet"

In teamA worksheet
I have following simple link from C-teamA
In cell A1 to I1400
=IF('C-TeamA'!B2<>"",'C-TeamA'!B2,"")

So I want to save teamA, teamB...etc into csv text file. If any help, it
will greatly appricated.