+ Reply to Thread
Results 1 to 6 of 6

Macro Programming - Hiding rows/columns based on certain text

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Macro Programming - Hiding rows/columns based on certain text

    Hi there,

    I have searched high and low on this forum and have come across some a code which allows me to hide a range of rows, but unfortunately it only hides the rows and/or columns you label in the macro.

    I have attached an example for those who may be able to assist.

    My goal is to be able to insert new rows and have the macro self-correct itself to then see an additional row, if I add a row to the example which I have done on sheet two, it will then only hide the first 4 rows and leave the 5th row showing, whereas I want it to be hidden also.

    Am I able to create a macro which would hide/unhide all rows which contain the data "123456", "55555", so that if I add a row between say rows 3 & 6 like in the example, it would automatically calculate this and when I run the macro it will hide all rows containing that data.

    I would like to keep the function the same as when the box is ticked it shows the cells and when unticked, it hides the cells containing "123456", etc..

    Hopefully I haven't confused anyone in my explanation.

    Many thanks in advance.

    James Tann
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Macro Programming - Hiding rows/columns based on certain text

    here's an example how to bind a dynamical range to a variable (altered to suit your example)

    Please Login or Register  to view this content.
    I'm sure that it can be done 10 times easier/faster/better but this should give you a basic idea how to approach your problem
    If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Macro Programming - Hiding rows/columns based on certain text

    Thank you.

    Would you be able to show an example of this incorporated into my existing code to hide/unhide? I'm not 100% on VBA coding and only just getting started.

    I have reviewed the code above to see if I can add it myself, added it to my macros, and selected it but cant seem to get it to work.

    Greatly appreciate your help.


    James Tann

  4. #4
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Macro Programming - Hiding rows/columns based on certain text

    sure thing. Here's one module containing a function to find the range which has to be hidden/unhidden for the first checkbox:

    Please Login or Register  to view this content.
    And here's the actual code to hide/unhide the rows

    Please Login or Register  to view this content.
    I think that it's quite obvious how to do the rest. Please be aware that my assumption was that each section ends with the word "Section".

  5. #5
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Macro Programming - Hiding rows/columns based on certain text

    Thanks Bishonen,

    Coding works great and I now only need one thing to complete it.

    In your code for the hide/unhide, you have set st = 3 which means this will start from row 3, which is correct, but when I copy this across to tick box 2 and say st = 8, if I add lines to the above set of information, it means that tick box 2 is thrown out of whack and only hides a couple of the previous entries above, whereas it should hide below.

    I have tried doing st = count(A3 + 3) so it will keep the st number at the correct position when the previous one is moved but I keep getting a compile error not matter which way I do it, is this line easily added or is it more complex? (I have set up A3 to do a simple count of the cells below to keep the st at the correct number)

    Many thanks.

    James Tann

  6. #6
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Macro Programming - Hiding rows/columns based on certain text

    Update: I have managed to incorporate the rows formula in my spreedsheet in conjunction with the st = cells(x,x).value and it's now self correcting when i had additional lines.

    Many thanks your assistance once again.

    Kind regards

    James Tann

+ 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