How Do I Quickly Tweak All Files in a Folder?


I recently got hooked on Tableau Public. It’s pretty awesome!

One of the downsides, however, is the need to find yourself some interesting “open” datasets to visualize.

As the number of prospective students at British universities has long been of interest to me, I thought I’d check out if UCAS had any publicly available data to crunch

Luckily for me, they did

Yet, the data format of those files was not what I would call an analyst delight – there were 6 extra rows on top of each dataset, which even the Data Interpreter in Tableau Public couldn’t handle

If I were to make a comprehensive analysis out of this data, I would have to spend at least an hour on data preparation – i.e. getting rid of the 6 extra lines from each of the 103 files

Good that I’ve got some VBA tricks up my sleeve! stillgotit

After using Power Query to generate a list of all the files saved in that folder, I fired up the following code, which would loop through every workbook on the list, open it, delete the first 6 rows and save it


The result?


You can find the interatctive workbook here

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.