My micro creates generates CSV that are a way to heavy!

[Jorge_Excel]

I created a macro for filling out long forms and one to create a CSV file of the final form. So far so good. The problem is that the CSV files were supposed to be around 100 kb and they are 25Mb! I am almost sure that they are heavy because they are carrying the macros along wth them. How to solve that?

The code of the macro that creates the CSV goes below:

'Create CSV:

Sub CopyToCSV()
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "C:\Users\catet7\Desktop\"


Sheets("Result").Select
MyFileName = Range("A2").Value & " " & "GENs" & " " & Format(Date, "dd.mm.yy")




'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
'Copies the sheet to a new workbook:
Sheets("Result").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("Result").Copy
'The new workbook becomes Activeworkbook:
With ActiveWorkbook
'Saves the new workbook to given folder / filename:
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Closes the file
.Close False
End With
Range("H16").Select


Sheets("Data Entry").Select
Range("C5").Select


End Sub

 

[Michael M]

Try changing this

'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
'Copies the sheet to a new workbook:
Sheets("Result").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
'Copies the sheet to a new workbook:
    With Sheets("Result").UsedRange
        .Copy
        .Value = .Value
    End With

 

[Jorge_Excel]

Mchael I see you wrote 2 codes starting with the same command. Am I supposed to try the first or the second?
Thank you

 

[Michael M]

Sorry, the first one was your code snippet.....the second one is what you should change it to !

 

[Jorge_Excel]

Richard,

I figured out what was going on, it is really weird. All the blank rows under the end of the used part of the file was counting as used ones as well if you know what I mean. So all I have to do is to select all those blank cells (with end command) and press delete. Weird!

Thank you for your help man.