excel - how not to skip empty first cell when saving as .CSV?

Question

I have a question regarding the way data is saved to CSV files. It only occurs when you are trying to save a single column with first cell(s) empty. You can select the whole column or a group of adjacent cells. As soon as you save it to CSV file, the leading empty cells are skipped. I was doing it as a part of a VBA sub but doing it manually hold same results. Below is the code I'm using and the test data.

Sub CopyRange()
'Copy range: suppose B1 and B2 are empty, B3 to B10 are filled with anything
'e.g. with some numbers
  ActiveSheet.Range("B1:B10").Copy
'Create new workbook
  Workbooks.Add

'NOTE: I need to create a workbook with generic name, save the name and pass it 
'to another program - just to clarity why I don't do it all in one shot at .Copy
  csvPath = TEMP & "" & ActiveWorkbook.Name & ".csv"

'Paste copied data to the newly created workbook
'NOTE: so far it keeps B1 and B2 empty
  ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Save the workbook as CSV
  ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, _
    ReadOnlyRecommended:=False, CreateBackup:=False
'At this point the saved file moves all data 2 cells up 
'to fill the empty spaces in B1 and N2. That's the issue!
End Sub

Interestingly enough, if you select more than one column then it saves the empty cells and starts data at the right location.

I tried using xlPasteValues instead of xlPasteAll which actually make matter worse - now it will skip empty cells even if you had more than 1 column, at PasteSpecial step.

I also tried to save it as xlCSVMSDOS, xlCSVWindows (both using " " End Of Line character) and xlCSVMac (which uses " |" EOL character). Nothing worked.

Please help!

P.S. I also tried to use the method described in this tread but still no luck.

Answer 1

When saving as a CSV, Excel seems to use the UsedRange for the sheet.

You can artificially expand the UsedRange by applying some formatting to the range. For instance, adding this line prior to the save would make it save the entire range.

ActiveSheet.Range("A1:A10").Interior.ColorIndex = 7

So just to give it to you in context the end of your sub would be this:

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, _
 Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Apply some color to the range so that it all gets saved in the CSV.
ActiveSheet.Range("A1:A10").Interior.ColorIndex = 7
'Save the workbook as CSV
ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, _
        ReadOnlyRecommended:=False, CreateBackup:=False
'Sheet gets saved as is with cell locations preserved.
End Sub