Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I am creating a set of csv files in VBA.

My script is creating the data set I need, but the number of rows differs in multiple iterations of the loop. For instance, for i=2, I have 100,000 rows, but for i=3, I have 22,000 rows. The problem is that when Excel saves these separate csv files, it does not truncate the space at the end. This leaves 78,000 blank rows at the end of the file, which is an issue given that I need about 2,000 files to be generated, each several megabytes large. (I have some data I need in SQL, but can't do the math in SQL itself. Long story.)

This problem normally occurs when saving manually - you need to close the file after removing the rows, then reopen, which is not an option in this case, since it's happening automatically in VBA. Removing the blank rows after saving using a script in another language isn't really an option, since I actually need the output files to fit on the drive available, and they are unnecessarily huge now.

I have tried Sheets(1).Range("A2:F1000001").ClearContents, but this does not truncate anything. Removing the rows should have similarly no effect before saving, since Excel saves all rows until the end of the file, as it stores the bottom-right most cell operated on. Is there a way to have excel save only the rows I need?

Here is my code used to save: (The truncation happens earlier, in the routing that calls this one)

Sub SaveCSV()
'Save the file as a CSV...
  Dim OutputFile As Variant
  Dim FilePath As Variant

  OutputPath = ActiveWorkbook.Worksheets("Macro").Range("B2").Value
  OutputFile = OutputPath & ActiveWorkbook.Worksheets("Macro").Range("B1").Value
  Application.DisplayAlerts = False 'DISABLE ALERT on Save - overwrite, etc.
  ActiveWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
  Application.DisplayAlerts = True 'DISPLAY ALERTS
End Sub

The relevant bit of code is here:

'While looping through Al, inside of looping through A and B...
'Created output values needed in this case, in an array...

Sheets(1).Range("A2:E90001") = Output

ActiveWorkbook.Worksheets(1).Range("F2").Formula = "=(does not matter, some formula)"
ActiveWorkbook.Worksheets(1).Range("F2").AutoFill Destination:=Range("F2:F90001")

'Set Filename to save into...
ActiveWorkbook.Worksheets("Macro").Range("B1").Value = "Values_AP" & Format(A, "#") & "_BP" & Format(B, "#") & "_Al" & Format(Al, "#")

'Save Sheet and reset...
Call SaveCSV
Sheets(1).Range("A2:F90001").ClearContents
CurrRow = 1

Next Al
share|improve this question
I added a piece of the code that calls the save routine. It's part of a system that I can't really share online, and the hundreds of lines of code used are kind of irrelevant to the question. Of course, all of this should be irrelevant if there is some way to accomplish the task - I don't just want "fixed" code as a solution, I want to know how to do this. – David Manheim Jul 12 '12 at 14:28

2 Answers

up vote 1 down vote accepted

You can get the UsedRange to recalculate itself without deleting columns and rows with a simple

ActiveSheet.UsedRange

Alternatively you can automate the manual removal of the "false" usedrange by deleting the areas below the last used cell with code such as DRJ's VBAexpress article, or by using an addin such as ASAP Utilities

share|improve this answer
up vote 1 down vote

Excel saves the UsedRange. In order to truncate the UsedRange, you need to delete whole rows and save the file.

If that's not an option, insert a new worksheet, copy the prepared data to it (thus leaving its UsedRange matching actual data), use Worksheet.SaveAs (as opposed to Workbook.SaveAs) and delete the worksheet.

Although the actual problem here is why your UsedRange gets that big in the first place.

share|improve this answer
Thank you for the suggestions. I cannot save the file without saving it as something - and the current file name is a csv that would overwrite the previous file. The Usedrange varies because, within the loop, some items have up to 1m rows output, and following items in the loop have many fewer. I will try inserting a new worksheet in order to save that worksheet, then removing it - but I am worried this will slow down the Macro immensely. – David Manheim Jul 12 '12 at 14:43
@DavidManheim You can also try to save the original file as an xls file into the system temp folder, truncating the usedrange, then immediately save it again as csv. – GSerg Jul 12 '12 at 14:46
That may be a better solution. Thanks. – David Manheim Jul 12 '12 at 14:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.