+ Reply to Thread
Results 1 to 15 of 15

Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

  1. #1
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Dear Excel Wizards,

    I am using Excel to create Proposals/Sales Orders/Service Orders/etc.

    I input all necessary information on the INFO Sheet, and it will auto populate and calculate all other sheets which are templates with their appropriate formulas. All is working well, and I have allotted 10 Lines to input different Line Items to display. I have created an overcomplicated VBA to auto-hide rows in my template sheets dependent upon if there is a QTY shown in my Line Items.

    THE PROBLEM
    The workbook works fantastically and other departments are requesting the same sheet. This is a problem as I would need to create this VBA for hundreds of new template sheets and have no desire to re-input the code for all sheets.

    THE CODE
    Please Login or Register  to view this content.
    CONCLUSION
    Please review and let me know if there is a simpler way to go about hiding the rows for each sheet and new sheets to be added.

    Note: I am rather new and self taught to Excel Macros and suspect I have approached this completely backwards.

    Any help is much appreciated!
    Last edited by 6StringJazzer; 08-09-2018 at 02:18 PM. Reason: code tags

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,673

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Welcome to the Forum GoyaRU !

    I suspect that it will be easy to answer your question once we figure out what it is.

    What sheets are the template sheets? Your code uses INFO plus a few other sheets with various combinations of tax and freight, but which ones are templates that would be added, what the "hundreds of new template sheets"?

    How are you going to create the new template sheets? BTW if you copy a sheet, Excel also includes the code in the copy. That alone may solve your question.

    The only thing I can tell you right now, which doesn't answer your question, is that your code can be streamlined. Your code is all If statements like this:
    Please Login or Register  to view this content.
    Each one can be a single line of code. The expression in your If statement evaluates to either True or False, so you can use it directly to set the Hidden attribute:
    Please Login or Register  to view this content.
    The parentheses on the right side are not required but I included them to make it more evident as to what's going on.

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Qu嶵ec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,386

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Hi,

    Here's a suggestion that you should be able to adapt to other templates.
    let me know if you have any questions

    Please Login or Register  to view this content.
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,100

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Please Login or Register  to view this content.
    could be replaced with

    Please Login or Register  to view this content.
    Last edited by mikerickson; 08-09-2018 at 02:20 PM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,793

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    How about
    Please Login or Register  to view this content.
    You'll need to add the other sheet names to the array.
    Alternatively how many sheets are there that you don't want to hide the rows on?

  6. #6
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Quote Originally Posted by 6StringJazzer View Post
    Welcome to the Forum GoyaRU !

    I suspect that it will be easy to answer your question once we figure out what it is.

    What sheets are the template sheets? Your code uses INFO plus a few other sheets with various combinations of tax and freight, but which ones are templates that would be added, what the "hundreds of new template sheets"?

    How are you going to create the new template sheets? BTW if you copy a sheet, Excel also includes the code in the copy. That alone may solve your question.

    Jazz,

    Thank you for the response. The issue may lie in the fact that the INFO sheet contains the Code and none of the other sheets do.

    Essentially, I fill out the INFO sheet with information such as Customer Name, Line Items, Quantities, Descriptions, Prices, Tax, Freight, etc. Then I click into the Appropriate Template which autofills based on the information provided on the INFO sheet.

    Thus, all sheets with the exception of INFO is a template sheet that is used based upon the the customer. (E.G. Whether or not we are charging Freight or Tax).

    Please let me know if this helps.

    Thanks,

    Goya

  7. #7
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,793

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Based on post#6
    try this instead of my code in post#5
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    All,

    Thank you for your replies.

    I'll give these a shot and report my success and/or failures.

    You all have been very helpful.

    Thanks!

  9. #9
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Quote Originally Posted by Fluff13 View Post
    Based on post#6
    try this instead of my code in post#5
    Please Login or Register  to view this content.
    Fluff,

    Of. Friggin. Course. It's so simple. Works perfectly and will be easy to modify in the future.

    You beautiful geniuses are fantastic. I think I'm in love.

    Thank you all for your help!

    Goya

  10. #10
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,793

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Glad we could help & thanks for the feedback

  11. #11
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,206

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Why not use a filter and simply filter based on your desired conditions?
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  12. #12
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Quote Originally Posted by Fluff13 View Post
    Based on post#6
    try this instead of my code in post#5
    Please Login or Register  to view this content.
    Fluff,

    Follow-up question for you. How can I modify this macro to also target an additional range ("B24:B29") and Hide target.row + 7?

    In other words, I am looking to repeat the same hiderow but in a different range further down the INFO sheet.

    Thanks,

    GOYA

  13. #13
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,793

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Maybe
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    08-09-2018
    Location
    Oregon
    MS-Off Ver
    2013
    Posts
    6

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    Quote Originally Posted by Fluff13 View Post
    Maybe
    Please Login or Register  to view this content.
    Sure enough, that did it!

  15. #15
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    4,793

    Re: Overcomplicated VBA for Simple Formatting - Need alternative to Hide Rows

    You're welcome

+ 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