Export Range of (Part of) an Excel Sheet to CSV

Question

I'm trying to figure out how to export only a portion of the sheet to csv without cycling through each row or cell and printing them individually to file. I need to maintain local formatting as one requirement.

Sheets("PI_OUTPUT").Copy

This copies the entire sheet. I have formulas in rows 1 to 20000 and columns A to X, but with a variable amount of blank rows after the data has been processed. If I use the copy method I copy all the empty rows, which outputs as rows of commas, etc.

I tried using activesheet.deleterows to trim the output file after the copy, but that gives me an error.

I've tried using:

Worksheets("PI_OUTPUT").Activate
 Sheets("PI_OUTPUT").Range("A2:X5000").Copy

I've tried to use 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 a number of sheets, all with variable number of columns and rows, all saved to separate files. So I'm looking for something I can repeat a number of times. I've got a folder path pop up to select the location, and have a dynamically constructed file name, but the copy/paste evades to file me.

M-- · Accepted Answer · 2021-04-26 14:35:19Z

11

You can use the code below;

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 "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

edited Apr 26, 2021 at 14:35

answered Jun 6, 2017 at 17:36

M--

33.6k12 gold badges74 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Itsallgonepearshaped Over a year ago

I got it to work: With wsh.Range("A1:X20000") .AutoFilter 1, "<>" 'Filter to get only non-blank cells wsh.Range(wsh.Cells(1, 1), wsh.Cells(1, 24).End(xlDown)).Copy I left out the second autofilter and tweaked the cell refs. Thanks for your help!

M-- Over a year ago

@Itsallgonepearshaped I was just debugging it. Yes. That was the problem. Glad that it was a help.

Collectives™ on Stack Overflow

Export Range of (Part of) an Excel Sheet to CSV

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related