title: Saving Excel data as csv with VBA - removing blank rows at end of file to save
body: <p>I am creating a set of csv files in VBA. </p>
  
  <p>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.)</p>
  
  <p>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. </p>
  
  <p>I have tried <code>Sheets(1).Range("A2:F1000001").ClearContents</code>, 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?</p>
  
  <p>Here is my code used to save: (The truncation happens earlier, in the routing that calls this one)</p>
  
  <pre><code>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 &amp; 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
  </code></pre>
  
  <p>The relevant bit of code is here:</p>
  
  <pre><code>'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" &amp; Format(A, "#") &amp; "_BP" &amp; Format(B, "#") &amp; "_Al" &amp; Format(Al, "#")
  
  'Save Sheet and reset...
  Call SaveCSV
  Sheets(1).Range("A2:F90001").ClearContents
  CurrRow = 1
  
  Next Al
  </code></pre>