Dear Readers,
A strong command in MS Excel is one of must have skill for every professional but an expertise in VBA is a lifesaver. Recently, I encountered a common situation where a platform had a limitation of accepting excel files with no more than 1000 rows. This means that if you have a single file of 1,00,000 records, then you would need to split it into 100 files with 1000 records each. Splitting and creating files manually at such point will be soulless, boring and dull task! This is where VBA comes to rescue….
Here is an Excel file which can be used to split a single Excel file into multiple Excel files basis any number of split row count. All you have to do is browse the source workbook (i.e. the one with 1,00,000 rows), select sheet name and provide the custom split count (i.e. 500, 1000..).
Download Link: Excel One to Many Files Splitter_V3.0
Input Dialog box for Excel file split Macro
Splitted files are created in the same folder as the source file
Hope this tool helps you and saves your time in day to day activities. Feel free to leave a comment, Like and Share this with your friends..
Follow me on Linkedin, Medium, GitHub for more stuff like this
Categories: Data Analytics, Data Science, MS Excel
How one can modify the no of columns and add more than one row as a header
Sorry for delayed response. I believe you are referring to having more than 1 row as header. Version 2 coming soon.
Hi Jayant, good day! Your works was a masterpiece! Just want to ask where could we see the version 2 in which we can edit the number of rows as a header
Life Saver – thanks a million for this
Thanks for the encouragement..Happy to help
Hi, I have 3gb file and file cant be open in excel as excel support data max till 2gb. As file is received in CSV format because of high data. how to add csv file type to current macro.
Hi, Version 2 has been uploaded to support csv and binary formats. Hope this helps!
Can you please share version 2 link. I run this version and it says Run-time error “9”: Subscript out of range. I have got 120095 rows
Hi Hardik, I have just uploaded V3 at the above link and tested the macro for maximum number of rows in Excel ~>1 million. If the problem persists, please share the raw data file (after removing any sensitive data) on my email jayant.kodwani@gmail.com..
Once I download the macro, How can I run it? there is no button or parameter box when I’m in it? thanks
Hi Pierre, Thanks for reaching out, The excel file has a big button on the main sheet. Please ensure that macros are enabled in your excel.
Kindly refer to this link for instruction on how to enable macros: https://support.microsoft.com/en-us/topic/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6
If the issue persists, please share the snapshot of your screen to Jayant.Kodwani@gmail.com
Hi Jayant, this is a genius creation. Thanks for helping other people, may God will help you in different direction.
Recently, I have a task that require this kind of action and it reduce the time consuming activities and make my work even more productive. I have avoid repetitive and manual job and make it easier to everyone in my team, thanks to you!
There is somehow a room to improve, I’m not sure if this is happening to everyone else but for my file, every single splitted file will capture the same data only on the first row. Not an issue for me but just want to highlight it to you.
A question as well: Is this feature also able to browse and upload multiple files? If let say, I have 100 excel files that I need to split to different files with certain # of rows. Would that be possible?
Thank you!
Looking forward for your reply.
Thank you Shai for recognizing the efforts. I am glad that the tool helped you. The code has been intentionally designed to include the first row as header in the split files as splitting is generally required in scenarios where files had to be uploaded on certain platform which match/validate the headers of the input files. Regarding the feature request, yes it is feasible and I will probably add it in version 4.