Split a Large Excel File into CSV Files by Category Using VBA
Question details
The user needs to divide a large dataset containing approximately 120,000 product records into multiple CSV files based on unique top-level categories in column G, while preserving headers and deleting empty columns.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Processing a massive product catalog or database export that needs categorization into separate, lightweight CSV files for easier importing or sharing.
- Observed behavior
- The user requires an automated VBA method to filter the data by unique values in a specific column, extract matching rows with headers, delete empty columns, and save them as individual CSV files.
Ensure you save a backup copy of your original large Excel file before running any VBA macros, and verify that macros are enabled in your spreadsheet trust center settings.
Automate Splitting Excel Data into CSV Files Using VBA
Using a VBA macro is the most efficient way to handle large datasets, filter unique values, remove empty columns, and generate sequentially named CSV files automatically.
To achieve this, you can adapt a standard 'split to new workbook' macro. Instead of saving the new instances as standard Excel files, you will instruct VBA to use the xlCSV file format.
Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' (or press ALT + F11). Once open, click 'Insert' > 'Module' to create a blank workspace for your code.
Write a script that uses a dictionary or an advanced filter to identify all unique top-level categories in your target column (e.g., Column G). Set the macro to apply an AutoFilter based on each unique category.
Command the macro to select the visible filtered rows, including the header row, and paste them into a newly generated workbook instance.
Add a loop in your code that reads the newly created workbook from the last column backwards to column A. Use 'WorksheetFunction.CountA(Columns(i))' to check for data, and apply 'Columns(i).Delete' if the column is completely empty.
Use the 'ActiveWorkbook.SaveAs' method, specifying the FileFormat parameter as 'xlCSV'. Name the file dynamically based on the current category value in the loop, then use 'ActiveWorkbook.Close' to complete the cycle.

Split and Manage Large Datasets Effortlessly with WPS Spreadsheet
WPS Office provides robust spreadsheet capabilities, including support for macros, allowing you to automate the process of splitting massive files into clean CSVs without performance lag.
- 1. Download and Install WPS Office: Get the latest version of WPS Office and ensure you have enabled the necessary macro components if prompted.
- 2. Open Your Large Dataset: Launch WPS Spreadsheet and open the large file containing your 120,000 product records.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the main ribbon and click on 'Macros' or the 'Visual Basic' editor to paste your splitting script.
- 4. Execute the Macro: Run the macro to automatically filter, copy, and generate the categorized CSV files in your designated local folder.

Frequently Asked Questions
Can I split the Excel file into CSVs without using VBA?
Yes, but for an extensive dataset with 120,000 records, it involves manually filtering column G, copying the visible data, pasting it into a new workbook, deleting empty columns, and saving as CSV for each category. Using VBA highly automates this repetitive process.
How do I remove completely empty columns using VBA?
Within your macro loop, after copying the filtered data to a new sheet, use a loop starting from the last column moving backwards to column 1. Check if 'WorksheetFunction.CountA(Columns(i))' equals zero. If it does, use 'Columns(i).Delete'.
Why does my CSV file lose formatting after saving?
CSV (Comma Separated Values) is a plain text format. It intentionally strips out all text formatting, cell colors, bold styling, and formulas to maintain a lightweight data structure suitable for database imports.
What if my target column has blank categories?
Your VBA script should include an 'If' condition to skip blank cells when generating the list of unique categories. This prevents the macro from throwing an error or attempting to create an unnamed CSV file.




