+ Reply to Thread
Results 1 to 21 of 21

Need to hide columns based on no or 0 value

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Red face Need to hide columns based on no or 0 value

    Hi Guys, Please see attached.

    I have a scored record which I wish to make as simple for the user as possible. It is in its basic stages at the moment and have already hit a hitch.

    Please note I wish the 'Shots' sheet to be the only place for the user to interact with.

    With this in mind it is not uncommon for players to not turn up at all and therefore their score would be a 0 for all attempts and shots.

    The 'Totals' sheet only uses data from the whole attempt and not individual shots, if the total figure for all attempts of a single player is 0; I would need this to hide the column, which would automatically remove that person from the chart. Omitting any ugly gaps automatically.

    This would mean that the user only needs to be considered with score keeping and not editting the worksheet.

    Please note that a player can use as many or as little attempts as possible. The lower the average score the better. Therefore if they score low on their first attempt they may not wish to have any more attempts. Therefore I only want to hide the column if no scores are recorded for all 3 attempts.

    Also the scoresheet will be blank before any games begin. Therefore I would like it to unhide the column upon the entry of data.

    The macro should also run in realtime as data is entered the "Totals" sheet updates.

    Please help, I have attached the spreadsheet and I have recognised the need for a macro but know absolutley nothing about how to make one.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: UNSOLVED. Need to hide columns based on no or 0 value

    Hi,

    I've added the macro to the attachment. I've hard-coded each range/person so if you change the worksheet you would need to change the macro as well. If you look in the vba editor in the worksheet "shots" object the code is in their. I think it is self explanatory but to add more people you would need to replace the Range("....") to where the sum is on the shots sheet and the Column("...") to the column number you want to hide if the sum of the shots is 0.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to hide columns based on no or 0 value

    Amazing I was definately not expecting a response that quickly yudlugar! Thank You .

    I do however have one more favour to ask. I've been playing with the code to try and get a zero value to remove more than one column. For example see my edit below in bold:

    Please Login or Register  to view this content.
    As you have probably worked out this does not work at all. Can you props
    Last edited by davesexcel; 04-26-2013 at 07:57 AM. Reason: code tags required, please read the forum rules!

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Need to hide columns based on no or 0 value

    That would be
    Please Login or Register  to view this content.
    Last edited by ragulduy; 04-25-2013 at 11:58 AM.

  5. #5
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Need to hide columns based on no or 0 value

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to hide columns based on no or 0 value

    That did the job perfectly, I find it strange that the columns can be referred to as numbers when selecting a single one and numbers for multiples...

    Thank you very much

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    Here is a code -

    Please Login or Register  to view this content.
    However this will hide cells with no value at all to. Is this what you want?

    Second Question - Is the range always going to be C5:I18, or will this change?
    Attached Files Attached Files
    Cheers!
    Deep Dave

  8. #8
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    I have edited the code slightly to suit your original Workbook -

    Please Login or Register  to view this content.
    Hope this helps!!

  9. #9
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    Thank you Deep. This is close but I dont want to hide rows with no value at all just zeros.

    Also instead of assigning the macro to a button I would need this to work on a 'live' basis (as the sheet is updated).

  10. #10
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    The second code does not hide rows with No Values. It only hides Rows with 0 Values.

    Regarding the 2nd line, ill send you an updated code.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    See attached file with macro.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: UNSOLVED. Need to hide rows based on no or 0 value

    Nice, but I need to send these to my clients in both print and .pdf form... so if we could have a solution that updates the spreadsheet automatically without the need to run the macro that would be kindly appreciated. also if the data then becomes a non zero value it should make the row reappear.

  13. #13
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Need to hide columns based on no or 0 value

    Please check the file attached now..

    Hope this is what you want..

    However I forgot to put a command for Print.. But I dont think that'd be a problem for you..
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Red face Re: Need to hide columns based on no or 0 value

    Thanks, apologies but when i update the back office page nothing happens. The row does not disappear or reappear. It only works if I edit the 'total value' on the printout page.

    The printout pages 'total value' is derived from a formula relating to the back office, therefore it needs to update based on the values in the back office.

    I really do apologise for no being more clear.

  15. #15
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Need to hide columns based on no or 0 value

    It does.. All you have to do is double click any cell in "Print Out" to trigger it.

    Thats the best I could suggest!!

  16. #16
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Need to hide columns based on no or 0 value

    Check it out.. This is slightly better..
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Need to hide columns based on no or 0 value

    Pl clarify that when you want printout.
    Eg; When a perticular cell updated.

  18. #18
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to hide columns based on no or 0 value

    Hi guys,

    I do not want a printout function, one of the worksheets is called printout thats all.

    WHat I need is for the 'printout' sheet to update automatically when the values on the 'back office sheet' are updated. Therefore on this attachment rows 12,16 and 17 should already be hidden but if the user was to input some corresponding values into the 'back office' then the total column on the 'printout page' should update and become visible. -and vice versa.

    This needs to be automatic and not connected to a button, but relative to the total value shown in the 'printout' page
    Attached Files Attached Files

  19. #19
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Need to hide columns based on no or 0 value

    Please see #16.

    It automatically updates. No buttons required!

    As soon as Back office Values are updated, the Print Out Sheet reacts accordingly.

    Thank You,


    Deep

  20. #20
    Registered User
    Join Date
    04-25-2013
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Need to hide columns based on no or 0 value

    Its not working for me. Can you try it with my latest spreadsheet?

  21. #21
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: Need to hide columns based on no or 0 value

    Works for me perfectly.

    I have downloaded the attachment from Post #18 an put the Macro.. Runs perfectly.

    Please try again.

    Also take a look at your Macro Security Settings.
    Attached Files Attached Files

+ 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