none
Hide Blank cells and Cells with a Zero Value RRS feed

  • Question

  • I have this macro that works for hiding all my cells in Column K that have a zero value based off the formula, However I also need it to hide any blanks that might be in Column K.  Can anyone tell me what I need to put in this macro to make it hide blanks and zero values in Column K?

    Sub HideRows()
        Dim cell As Range
        For Each cell In Range("k:k")
            If Not IsEmpty(cell) Then
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                End If
           
                End If
        Next
    End Sub

    Monday, October 16, 2017 8:30 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, October 17, 2017 2:28 AM
  • Hi,

    I made a sample VBA:
        
    Here is my code:

    ' --- [Hide] button --------------------

    Private Sub btn_Hide_Click()
    ' --- for debugging Dim startDT As Date Dim stopDT As Date startDT = Now() ' --- main process Application.ScreenUpdating = False Dim maxRow As Long: maxRow = Rows.Count Dim lastRow As Long: lastRow = Range("K" & maxRow).End(xlUp).Row Dim myRow As Long ' --- Hidden: from row 1 to lastRow(value exists) For myRow = 1 To lastRow If (Cells(myRow, 11).Value = "") Or _ (Cells(myRow, 11).Value = 0) Then Rows(myRow).EntireRow.Hidden = True End If Next ' --- Hidden: from row lastRow + 1 to maxRow (which varies Excel version) Dim cellLeft As Range Set cellLeft = Range("K" & lastRow + 1, "K" & maxRow) cellLeft.Rows.Hidden = True Application.ScreenUpdating = True ' --- for debugging stopDT = Now() MsgBox "completed:" & Chr(13) & "startDT=" & startDT & Chr(13) & "stopDT =" & stopDT Application.ScreenUpdating = True End Sub
    ' --- [Show] button ------------------------------ Private Sub btn_Show_Click() Application.ScreenUpdating = False Dim maxRow As Long: maxRow = Rows.Count Dim allCells As Range Set allCells = Range("K1", "K" & maxRow) allCells.Rows.Hidden = False Application.ScreenUpdating = True End Sub


    Regards,

    Ashidacchi

    • Proposed as answer by Terry Xu - MSFT Wednesday, October 18, 2017 2:08 AM
    Tuesday, October 17, 2017 3:02 AM
  • Hi Angela P Smith,

    you just need to modify your code like below.

    Sub HideRows()
    
         Dim cell As Range
         For Each cell In Range("K:K")
             If IsEmpty(cell) Or cell.Value = 0 Then
                
                     cell.EntireRow.Hidden = True
            Else
             
                 End If
         Next
     End Sub
    

    so in the same condition you can check that cell is empty or contains 0 or not.

    but here you need to find the last used cell in column K and pass it in range.

    so that the code work on specific range.

    otherwise it will work on whole column till the end and because all cells are empty after the last used cell the code will run continually and VBA Editor and Excel will be hang.

    to again make the cell visible you just need to  set  cell.EntireRow.Hidden =false in code and same code you can use to again display the cell.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 18, 2017 7:39 AM
    Moderator