How to save / export only filtered data from Excel to csv file?
As we all known, it is easy for us to save a worksheet as csv file. But, have you ever tried to save only filtered data as csv file? Normally, the Save As feature In Excel will save the whole worksheet data as csv file, in this case, how could you save or export only filtered data from Excel to csv file?
In Excel, you may not save the filtered data to csv file directly, but, you can finish it with a workaround, please do as follows:
1. Select the filtered data that you want to save.
2. Then click Home > Find & Select > Go To Special, see screenshot:
3. In the Go To Special dialog box, choose Visible cells only option, see screenshot:
4. Then click OK button, and then copy and paste the filtered data to a new worksheet, and only the filtered data has been pasted into the new worksheet.
5. Now, you can save this new worksheet as csv file, please click File > Save As, in the Save As dialog box, specify a name and location for this file, and select CVS(Comma delimited) from the Save as type drop down list, see screenshot:
6. Then click Save to save this filtered data, and some warning messages will pop out, please click OK and Yes to close the messages, and then only the filtered data is saved as csv file in your selected directory.
It may be an inefficient way by using the above method, here, I can create a VBA code for helping you export the filtered data to csv file quickly and conveniently.
1. Hold down the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Save / Export only filtered data from Excel to csv file
Sub Macro1() 'updateby Extendoffice Dim xRg As Range Dim xCell As Range Dim xAddress As String Dim xUpdate As Boolean Dim xFileName As String Dim xWb As Workbook On Error Resume Next xAddress = Application.ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("please select the filtered range", "Kutools for Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Set xRg = xRg.SpecialCells(xlCellTypeVisible) If xRg Is Nothing Then Exit Sub xFileName = Application.GetSaveAsFilename(, "CSV Files (*.csv), *.csv", , "please specify the file name") If xFileName = "" Then Exit Sub xUpdate = Application.ScreenUpdating Application.ScreenUpdating = False xRg.Copy Set xWb = Application.Workbooks.Add xWb.ActiveSheet.Paste xWb.SaveAs Filename:=xFileName, FileFormat:=xlCSV, CreateBackup:=False xWb.Close False Application.ScreenUpdating = xUpdate End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you select the filtered data that you want to save as csv file only, see screenshot:
4. Then click OK button, and another box is popped out to remind you specify a file name and a location for your csv file, see screenshot:
5. And then click Save button, only the filtered data is saved as csv file.
Kutools for Excel contains a useful tool-Export Range to File, it can help you export or save a range of selected cells to csv, pdf, text and other formatting files.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:
1. Select the filtered data to export.
2. Click Kutools Plus > Import & export > Export Range to File, see screenshot:
3. In the Export Range to File dialog box, choose the file format that you want to save under the File format, then then click button to select a folder to save the exported file, see screenshot:
4. Then click Ok button, and another prompt box is popped out to remind you type a name for your exported file, see screenshot:
5. And then click Ok, and only the selected filtered data has been saved as csv file as you need.