Unhiding all or a range of hidden rows in Excel

Question

I am trying to find how to unhide Excel rows for a range and for an entire worksheet. I followed all the methods outlined on the WikiHow site, but unfortunately these do not work. The hidden rows were a consequence of filtering unique values in a long list. Now I am ploughing through some 200 hidden rows revealing them individually, but I would like to know why the bulk methods no longer work.

Answer 1

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Answer 2

@alancsears.... There are only 3 scenarios that need to be explained. They can be explained in simple English; no need for videos (that move too fast for me). And it does not matter why the rows are hidden.

Consider the following paradigm:

1. Beginning rows hidden

Enter 1:10 into the Name Box, where 10 is the first visible row.

Right-click "10" (first visible row number) and click Unhide.

2. Rows in between hidden

Enter 12:23 into the Name Box, where 12 and 23 are the two closest visible rows.

Or select row numbers "12" and "23" by dragging the mouse cursor.

Right-click "12" or "23" (either visible row number) and click Unhide.

Note: There is no need to carefully click in between the two rows, as wikihow describes.

Also note: You can select multiple ranges. For example, type literally 12:23**,**25:36 into the Name Box. Or type 12:36. Or select the ranges by dragging the mouse cursor. Then right-click any visible row number in the group and click Unhide.

Caveat: If you use comma as the numeric decimal point, you might need to use semicolon instead of comma to separate ranges in the Name Box.

3. End rows hidden

Enter 38:1048576 into the Name box, where 38 is the last visible row.

Right-click "38" (last visible row number) and click Unhide.

Note: I find it difficult to remember the last row number, 1048576. So I open another instance of Excel and press ctrl+downArrow to see the last row number.

---

PS.... Re: Now I am ploughing through some 200 hidden rows revealing them individually, but I would like to know why the bulk methods no longer work.

Forgive me if the above is "old news".

As a corollary, if you want to unhide the entire worksheeet, enter 1:1048576 into the Name Box, right-click any visible row number of the left, and click Unhide.