+ Reply to Thread
Results 1 to 8 of 8

Hide Zero Values

  1. #1
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Hide Zero Values

    Hi,

    Can anyone help me to filter and hide rows if Column AS to AV has zero values until last row.

    Regards
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Hide Zero Values

    Hi,

    I'd use a helper column that summed the AS:AV values and then used that column to filter for non zeros.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2016 | 2019
    Posts
    438

    Re: Hide Zero Values

    Try
    PHP Code: 
    Sub HideRows()
        
    Dim i As Integer
        Application
    .ScreenUpdating 0
        
    For 6 To 400
            
    If Sheets(1).Cells(i45).Value And Sheets(1).Cells(i46).Value And Sheets(1).Cells(i47).Value And Sheets(1).Cells(i48).Value 0 Then
                Worksheets
    ("Sheet1").Rows(i).Hidden True
            End 
    If
        
    Next
          Application
    .ScreenUpdating 1
    End Sub 
    Last edited by jeffreybrown; 04-08-2017 at 09:08 AM.

  4. #4
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Hide Zero Values

    Thanks, the works perfectly. The rows will not be fixed always, there is huge data in my original file. Can I request you to set range for the last used row.

    Regards,

  5. #5
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2016 | 2019
    Posts
    438

    Re: Hide Zero Values

    Quote Originally Posted by Barieq View Post
    Thanks, the works perfectly. The rows will not be fixed always, there is huge data in my original file. Can I request you to set range for the last used row.

    Regards,
    Change:
    PHP Code: 
    Sub HideRows2()
        
    Dim lr As LongAs Long
        lr 
    Range("A65000").End(3).Row
          Application
    .ScreenUpdating 0
        
    For 6 To lr
            
    If Sheets(1).Cells(i45).Value And Sheets(1).Cells(i46).Value And Sheets(1).Cells(i47).Value And Sheets(1).Cells(i48).Value 0 Then
              Sheets
    (1).Rows(i).Hidden True
            End 
    If
        
    Next
          Application
    .ScreenUpdating 1
    End Sub 

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Hide Zero Values

    ... a word of caution

    If there is indeed a huge number of rows in your data, looping through cells with a macro will take a long time. That's because there is a time overhead each time VBA jumps back to Excel to evaluate and take action and then returns to VBA

    The fastest and most efficient way I know of doing this sort of stuff is to filter the data for the stuff you want to hide and then use a single line of 'your_range.SpecialCells(xlCellTypeVisible).Hidden = True

  7. #7
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Hide Zero Values

    Thanks, Solved

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,418

    Re: Hide Zero Values

    Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Hide Values Until Additional Values Are Entered
    By DrillinHole in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-14-2015, 03:43 PM
  2. Hide Rows with a Value (2 values to hide) then delete visible rows
    By Slea in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-19-2013, 06:25 AM
  3. Hide Zero Values
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-12-2011, 05:28 AM
  4. Excel 2007 : Hide values
    By vanmeterkj in forum Excel General
    Replies: 0
    Last Post: 08-01-2011, 12:23 PM
  5. Hide Zero Values
    By winnie_shrub in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-10-2007, 09:48 PM
  6. Hide values
    By PaulOakley in forum Excel General
    Replies: 1
    Last Post: 04-03-2006, 08:10 AM
  7. Hide all values in a row if value is 0
    By Erik T in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2006, 08:25 PM

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