How to Extract Unique Values From Hidden Columns Across Multiple Worksheets Using VBA

Photo by Kelly Sikkema on Unsplash

If you are reading this article right now, you must be looking for an answer to one of these questions in Excel:

I. How to unhide columns across multiple worksheets

II. How to extract unique values from columns across multiple worksheets

Or both.

In this article, I want to share how I solve these problems with simple VBA and PivotChart Wizard.

Below are the screenshots of my mock data in Excel. Each screenshot represents data in each worksheet/tab. Let’s say what we want to do is create a list of unique jobs (column D).

I create only 3 worksheets for demonstration, but this technique is useful especially when there are many worksheets, and when manual work is almost impossible.

The above screenshots show all data, but when I received the data, column D called ‘Job’ was hidden across multiple worksheets, so first, I will show how to unhide columns.

To unhide columns across multiple worksheets, press Alt + F11 and insert a VBA module.