Have you ever encountered a filtered range without Filter drop-down buttons available on the top? No idea what I am talking about? Take a look at the following screenshot.
Without the Filter buttons on the top, I tried to show hidden rows by unhiding rows without success…
So what to do to show all value?
No panic. What you saw was a result from Advanced Filter. You may clear the filter easily: Data->Filter->Clear
Here we go!
If your hidden rows was not a result from advanced filter, you may want to read When unhide rows doesn’t work.
Question:
How can we filter a specific date ignoring years? For example, show only 1st of January of all years (say you have 10-years data). If you know how, please feel free to share with us by leaving a comment. Answers to be posted next week. 🙂
wmfexcel 
Thanks! I wasted an hour on this before finding this article!
LikeLike
Welcome! Glad it helped. 😀
LikeLike
Didn’t work for me, Excel 365. But what did was to block the rows I couldn’t unhide, hide them again, then unhide. All rows unhid, not just the last ones I hid.
LikeLike
Wrong location. Should be on whole article.
LikeLike
Thank you so much! I was going crazy trying to figure out why I could not unhide my rows……something so simple as data, filter, clear! Thanks again!
LikeLike
You are welcome. Glad it helped.
LikeLike
THANKS
LikeLike
Welcome 😀
LikeLike
Thanks for saving my sanity!!!!
LikeLike
Glad it helped! 😀
LikeLike
Thank you for this… an hour of frustration gone in an instant 🙂
LikeLike
Glad it helps! 😀
LikeLike
love it, thank you for this! I was getting pissed off trying to figure it out using row heights.
LikeLike
You are welcome. Glad it helps.
LikeLike
I know it’s late, but you just saved my life.
LikeLike
You are welcome. Glad it helps.
LikeLike
Do pivot report from your data after adding a helper column to source containing dd-mm without year
Then filter by required date of month
LikeLike
Thank you. I had filters on. I had forgotten that!
Cheers
Steve
LikeLike
Thanks for saving my life….. 🙂 I was just about to go crazy if it didn’t work …. but magically it work for me.. thanks thanks thanks.. (1000 times)
LikeLiked by 1 person
Glad it helps. Cheers
LikeLike
I was so hoping this would work. I have a “hidden” column A. I’ve tried selecting it and resizing. I’ve tried “unhiding” the whole spread sheet. I’ve tried everything, including this…. which had me excited. Sadly, the CLEAR option was not available. But I’ll remember it for another time.
Until then I have a hidden column A (or A1\ as the spreadsheet insists on saving as the option… which may be the only clue as to what is happening here) that I can’t get to. Very very odd.
LikeLike
Hi Lindley, have you checked if the pane is frozen??
LikeLike
The worksheet was frozen. Duh. Thank you for pointing out another possibility. 🙂
LikeLike
You are welcome
LikeLike
Yes! That worked. Thanks Mickie. Sometimes it’s the obvious answers we overlook. But meanwhile, I’ve learned a whole bunch of useful tips.
LikeLike
Thanks a million – have stressed out for some hours trying to ‘fix’ my spreadsheet and in a panic as I couldn’t figure out where my stock take rows and columns had gone! Done now and very happy. Your instructions were clear and easy to follow and VERY accurate!
LikeLike
Hi Lori
Thanks for your kind words. Glad it helps.
LikeLike
Hey smarty pants. I need some help. I would like to set up a view of a sheet that hides dup values occurring in one column. I want both views available for the user. One without dups and one with dups. Can you assist? I sure hope you are still around.
LikeLike
@Les Logar,
Assuming your values are in column “A” starting in cell A1 (no empty cells in range) – try to run (one at a time) the two following Macros
I used the “Conditional Formatting” feature to change the duplicates font color into White.
Sub Hide_Duplicates()
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(“A1:A” & LR)
Rng.FormatConditions.AddUniqueValues
Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
Rng.FormatConditions(1).DupeUnique = xlDuplicate
With Rng.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
End Sub
Sub Unhide_Duplicates()
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(“A1:A” & LR)
Rng.FormatConditions.Delete
End Sub
Michael (Micky) Avidan
LikeLike
As a matter of fact – I would slightly change the Code to the following:
Public LR As Integer, Rng As Range
Sub Hide_Duplicates()
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(“A1:A” & LR)
Rng.FormatConditions.AddUniqueValues
Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
Rng.FormatConditions(1).DupeUnique = xlDuplicate
With Rng.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
End Sub
Sub Unhide_Duplicates()
Rng.FormatConditions.Delete
End Sub
Michael (Micky) Avidan
LikeLike
Two “fast” ways to filter a specific date (1st of January) by ignoring the years:
1) Add the formula to an Helper Column to be filtered by: =TEXT(A2,”MMDD”) and copy down.
2) Use Advanced Filter with a criteria: =AND(DAY(A2)=1,MONTH(A2)=1)
3) OR Use Advanced Filter with a criteria: =DAY(A2)+MONTH(A2)=2
[This one is valid for the 1st of January only]
Michael (Micky) Avidan
LikeLike
Hi Micky,
Thanks for your input. They all work well!
Actually, your solution 1) will be part of my next post.
Cheers,
LikeLike