My macro creates CSV files but they are carrying over all the blank cells as well so they're extremely heavy.

[Jorge_Excel]

How can I solve this problem? I tried recording a macro that finds all the blank cells (with F5) but that finds billions of cells.

My code for the CSV exporter is currently:

Sub NewCopyToCSV()
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 & " CSV created on " & " " & 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").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
End Sub

 

[sijpie]

I don't understand, because your code unchanged (other than the file path) works perfectly and creates a small .csv

I wonder if you have a cell containing something way deep in your sheet..

In the sheet Result, press Ctrl-End. Which cell does that move you to? If that is IV234567 or something it would mean that you could get loads of extra bagage. See what is causing it. Some formatting or so?

Easiest to get rid of it is to delete all columns to the right of your range of interest and all rows below. Make sure first that there are no columns or rows hidden.

 

[Jorge_Excel]

Deleting all the extra sounds like what I am lookng for. How to do that? What is the code?

 

[sijpie]

Just do it by hand! It should be a once off only. Do what i explained above. It could also reduce your file size considerably as a bonus

 

[Jorge_Excel]

Just like you guessed, when I pressed Ctrl End the cursor went all the way down to V1048576. Then I hit F5 to select all the blanks and delete and Excel is not responding anymore. God ****! Thanks man

 

[sijpie]

The alternative is to copy just your used range to a new sheet, format it and then delete the other sheet.

Although Excel is supposed to be able to handle it, there is usually an issue when you have stuff close to the bottom border and try to get rid of it...

good luck, keep smiling, in the end it 'll run better.