I'm a VBA noob, need help with VBA script!

  • [xpost][/xpost]

    Hi all,


    I'm a complete noob when it comes to VBA scripts, I've never used it before and I'm not a programmer at all. I found a VBA script online and I don't know enough to modify it to suit my needs.


    The script has 2 functions - HideRows and ShowRows. When you run them, you are asked to specify a range. For example, I can provide the following range:

    'Sheet 1'!H7:H24


    It will then look at the cells at H7~H24 in Sheet 1 and hide or unhide the row(s), depending on whether the cell value is a 0 or not.


    What I'm trying to do is to provide multiple ranges from multiple sheets. E.g:

    'Sheet 1'!H7:H24

    'Sheet 2'!D5:D19


    The problem is that the VBA script seems to only allow one range at a time and I need to modify this to allow multiple ranges.


    For reference, I have attached a test Excel spreadsheet for you to play around with.



  • You don't need most of your code, this will do what you want.


    Code
    Sub ShowHideRows()
    Sheet1.Range("H7:H24").EntireRow.Hidden = Not Sheet1.Range("H7:H24").EntireRow.Hidden
    Sheet2.Range("D5:D19").EntireRow.Hidden = Not Sheet2.Range("D5:D19").EntireRow.Hidden
    End Sub

    Read these links to learn where to put the code and how to rub it.


    Where to put the code


    How to run the code

  • Sorry, but that's not a good solution for me. Your code assumes that the range is not going to change and is static. You've essentially hardcoded the range into the solution. The code I shared allows you to define a range and this can be any range from any sheet. This makes it flexible and is what I need. All I'm looking for here is to add multiple ranges from multiple sheets into the inputbox. At the moment, I can only feed just one range into it.

  • Try this, the Input box must be completed as follows:


    Sheet Name & comma & Range & semi-colon & Sheet Name & comma & Range and so on. For example::


    data 1,H7:H24;data 2,O9:O26

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks, I'll give it a try at work tomorrow.


    Just for my learning purposeS, could you explain what your code does PLEASE? I sort of get parts of it. I think you're trying to split the symbols ',' and ';' in order to process the 2 different ranges from the 2 different sheets separately. I kinda get the hidden = 1 and 0, which is to hide or unhide the rows. But I don't quite get the rest and my coding knowledge and skills is just rubbish......

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this and read the Forum Rules before posting again.



    https://www.excelguru.ca/content.php?184

  • I am attaching your sample workbook with the code so you can see it working. I have added a button to run the code.


    Here is the code with comments to help explain what it is doing

  • Sorry, my mistake, misunderstood that part of your request.


    Change the code to this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!