Export Range of Cells in Excel to CSV File

CSV (Comma?Separated Values) is a plain text file format used to store tabular data. It is a widely supported and commonly used format for exchanging data between different applications, particularly in spreadsheet software like Microsoft Excel, Google Sheets, and others. When working with CSV files, it's essential to be mindful of the delimiter used, handle special characters appropriately, and ensure data integrity and consistency.

Steps to Export a Range of Cells in Excel to CSV

To export range of cells in Excel data to CSV (Comma?Separated Values) format, you can follow these steps:

Step 1

Open your Excel workbook where the data contains. Press Alt+F11 to open the VBA Editor. Insert a new module by clicking Insert ?> Module.

Copy and paste the following code in to the newly added Module.

Example

Sub ExportRangeToCSV()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim savePath As String
    Dim saveFileName As String
    Dim rng As Range
    
    ' Set the workbook and worksheet variables
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
    
    ' Prompt user to select the range
    On Error Resume Next
    Set rng = Application.InputBox("Select the range to export:", Type:=8)
    On Error GoTo 0
    
    ' Check if user canceled the selection
    If rng Is Nothing Then
        Exit Sub
    End If
    
    ' Prompt user for save location and filename
    savePath = Application.GetSaveAsFilename(FileFilter:="CSV Files (*.csv), *.csv")
    
    ' Check if user canceled the save dialog
    If savePath = "False" Then
        Exit Sub
    End If
    
    ' Get the file name from the full path
    saveFileName = Dir(savePath)
    
    ' Export the range to CSV
    With CreateObject("Scripting.FileSystemObject")
        Dim file As Object
        Set file = .CreateTextFile(savePath, True)
        
        Dim row As Range
        For Each row In rng.Rows
            Dim cell As Range
            For Each cell In row.Cells
                file.Write cell.Value & ","
            Next cell
            file.WriteLine
        Next row
        
        file.Close
    End With

    MsgBox "Selected range exported to CSV successfully."
End Sub

Step 2

Modify the code to set the correct worksheet name and range you want to export. Save and close the VBA Editor.

Press Alt+F8 to open the Macro dialog box. Select the ExportRangeToCSV macro and click Run.

Step 3

After clicking on the Run the code will prompt for selecting the Range of cells that you want to export.

Step 4

After selecting the Range and clicking on OK then the code will prompt for Save As dialog, there you need to enter the filename and the desired location where you want to save the CSV file with default Save as type as CSV File.

Step 5

After clicking on the Save the file will be save in the desired location.

The file and the output would look like the one shown below:

Conclusion

In summary, exporting a selected range of data from an Excel worksheet to a CSV file using VBA is entirely feasible. By utilizing VBA code, users can dynamically select the desired range during the macro execution, prompting an interactive selection process. The code provided successfully exports the chosen range to a CSV file, eliminating the repetition of header rows. This streamlined solution ensures that the exported CSV file contains the relevant data without redundancy.

With this VBA functionality, users can efficiently export specific data ranges from their Excel workbooks to CSV files, simplifying data management and facilitating further analysis or integration with other applications.