+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-09-2019
    Location
    Amsterdam,Netherlands
    MS-Off Ver
    2016
    Posts
    3

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

    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?

  2. #2
    Forum Expert
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    1,443

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

    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.
    Last edited by JLGWhiz; 06-09-2019 at 06:52 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, click the 'Go Advanced' button, Then click 'Manage Attachements' and follow the menu.
    Thinking I am smart and knowing I am smart are two entirely different levels of schizophrenia.

  3. #3
    Registered User
    Join Date
    06-09-2019
    Location
    Amsterdam,Netherlands
    MS-Off Ver
    2016
    Posts
    3

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

    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?

  4. #4
    Forum Expert
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    1,443

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

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

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-09-2019
    Location
    Amsterdam,Netherlands
    MS-Off Ver
    2016
    Posts
    3

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

    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

  6. #6
    Forum Expert
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    1,443

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

    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.
    Last edited by JLGWhiz; 06-10-2019 at 11:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1