Export range (part) of Excel sheet to CSV with VBA
I know that export to csv has been set about a billion times and I have done it quite a few times, but I am trying to figure out how to export only a portion of a sheet to csv without looping through each row or cell and print them out separately per file. I need to support local formatting as one of the requirements.
Sheets("PI_OUTPUT").Copy
This copies the entire leaf, warts and that's it. I have a formula in rows 1 to 20,000 and columns A to X, but with a variable sum of blank rows after processing the data. If I use the copy method, I copy all blank lines that are output as a series of commas. ,,,, etc ...
I tried using activesheet.deleterows to truncate the output file after copy, but that gives me an error.
I've tried using:
Worksheets("PI_OUTPUT").Activate
Sheets("PI_OUTPUT").Range("A2:X5000").Copy
I tried another suggestion: specialcells (xlCellTypeVisible), but I can't seem to get it to work:
Set rng = Sheets("PI_OUTPUT").SpecialCells(xlCellTypeVisible)
Set Workbook = Workbooks.Add
With Workbook
Call rng.Copy
.Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
.SaveAs filename:=MyPath & MyFileName, FileFormat:=xlCSV
.Close
End With
To make it worse, I have to do it across multiple sheets, all with a variable number of columns, rows, all stored in separate files, so I'm looking for something I can repeat multiple times. I have a folder path to select a location and have a dynamically built filename, but copy / paste eludes me.
Currently ripping what little hair I got after countless google searches, any help is greatly appreciated.
source to share
You can use the following code:
Sub testexport()
'Adpated from OzGrid;
' export Macro as CSV
Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("PI_OUTPUT")
With wsh.Range("A2:X20000")
.AutoFilter 1, "<>" 'Filter to get only non-blank cells
'assuming there is no blank cell within a filled row:
wsh.Range(wsh.Cells(2, 1), wsh.Cells(24, 2).End(xlDown)).Copy 'copy non-blank cells
'.AutoFilter should not cancel autofilter as it clears cutcopy mode
End With
Application.DisplayAlerts = False 'avoid from "save prompt window"
Workbooks.Add
ActiveSheet.Paste
'Saves to C drive as Book2.csv
ActiveSheet.SaveAs Filename:= _
"C:\Book2.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
wsh.Range("A2:X20000").AutoFilter 'clear the filter
Application.DisplayAlerts = True 'set to default
End Sub
source to share