Auto Hide rows VBA taking Long (I've got no clue, pls)

[idsie]

I've recently started using excel, I've never used macros/vba before. Let's just say I haven't got a clue as to how to construct them.

My case is the following: I have a list with date over several years, Column A10 downwards contain the year (every row with info about 2015 is designated 2015 in the according A Row). I wish to auto hide rows and only show rows with the a year of my choice (A1 contains the year I want to view, contains a dropdown list).

One final important thing, I still want to be able to add more info underneath lowest row containing values. This is the reason Vlookup to different sheets wouldn't work out.

The code I've found(Literally found) to be working =

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" And Target.Cells.Count = 1 Then

Application.ScreenUpdating = False

Range("A10:A100").EntireRow.Hidden = False

Dim mgrList As Range
Set mgrList = Range(Range("A10"), Range("A10").End(xlDown))

Dim mgrCheck As Range
For Each mgrCheck In mgrList
mgrCheck.EntireRow.Hidden = mgrCheck <> Target
Next

End If

End Sub

The only problem is, it takes very very long to update the rows when the value in cell A1 is changed. Even for just Range A10:A100 it will take several minutes, whilst I need a minimum range of A10:A1000.

Can you gods tell guide me to a way for this not to take this long?

[JLGWhiz]

This might be a little faster.

Please Login or Register  to view this content.

BTW, you need to use code tags to put the code you paste on this forum into the box. To do that, use the mouse pointer to select the code (shade it) then click the hash mark (#) symbol. The code tags will be placed before and after your code so it will appear as mine does.

[idsie]

Thanks for the fast reply, I've given it a quick try.
I've replaced the old code with the one you gave me. But it comes back with a compile error Sub or Function not defined. It highlights the top row, and it seems to highlight the RangeI in the for each migrcheck line

Is this something I've messed up?

[JLGWhiz]

I should have tested it before I posted it. This one should work OK.

Please Login or Register  to view this content.

[idsie]

I really want to thank you for writing these, however a new problem has turned up. If I select a year(2018), it will hide all rows accordingly, then if I select a lower year(2016) everything will be hidden, it does not unhide the rows that contain that year.

It's super fast though and I'm loving it. Just to bad it doesn't unhide the proper lines aswell hahaha

From what you gave me last night i've done some research and inserted the calculation lines. This is already a lot faster than what I had and works as intended, but not nearly as fast as what you gave me. (Takes good 30 sec, yours takes 1 or 2 sec)

Please Login or Register  to view this content.

If I'm being a bother please let me know, I know how frustrating it can be explaining stuff to a newb haha

[JLGWhiz]

This got rid of the problem of not finding the A1 value. It was related to the last row variable being changed without the full range of data in column A.

Please Login or Register  to view this content.

If your issue is satisfied, don't forget to mark the thread as solved.