Member-only story

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

Deborah Kewon
4 min readJun 14, 2023

--

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.

--

--

Deborah Kewon
Deborah Kewon

Written by Deborah Kewon

Data Analyst with a degree in International Relations— Lifelong Learner — Explorer

No responses yet