Announcement

Collapse
No announcement yet.

Hide rows if all cells within the rows are blank

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hide rows if all cells within the rows are blank



    Hello

    I am trying to hide rows where all cells within in it are blank. The code I have is

    Code:
    Dim s As Long
    Dim rng As Range
    
    
    With ActiveSheet
    
    For s = 67 To 79
    Set rng = .Range(.Cells(s, 1), .Cells(s, 17))
    .Cells(s, 1).EntireRow.Hidden = WorksheetFunction.Sum(rng) = 0
    Next s
    
    End With
    
    Set rng = Nothing

    The code does hide the rows but also hides rows if text is entered within a cell.

    Thank you in advance for your assistance.

  • #2
    modified as follows

    Code:
    Sub test()
    Dim s As Long
    Dim rng As Range
    
    
    With ActiveSheet
    
    For s = 67 To 79
    Set rng = .Range(.Cells(s, 1), .Cells(s, 17))
    If WorksheetFunction.Sum(rng) = 0 Then
    If WorksheetFunction.count(rng) = 0 then
    .Cells(s, 1).EntireRow.Hidden = True
    '.Cells(s, 1).EntireRow.Hidden = WorksheetFunction.Sum(rng) = 0
    End if
    End if
    Next s
    
    End With
    
    Set rng = Nothing
    End Sub
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Thanks for your reply.

      I have tried the revised code you kindly supplied. Unfortunately, it still hides the rows where there is text in one of the cells.

      Thanks in advance

      Comment


      • #4
        Upload a sample workbook to this forum so that we can test and not continue to guess at your structure.
        Alan

        Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
        FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

        If someone has helped you, say "thank you" by clicking on the Like Button.

        Comment


        • #5
          I have attached a draft workbook with the macro.

          Book1.xlsm

          Kind regards.

          Comment


          • #6
            change the worksheet function Count to CountA
            Alan

            Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
            FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

            If someone has helped you, say "thank you" by clicking on the Like Button.

            Comment


            • #7


              With your sample workbook, try
              Code:
              Sub HideRows()
                  Dim i As Long
                  
                  With ActiveSheet
                      For i = 2 To 17
                          .Rows(i).Hidden = Application.CountA(.Cells(i, 3).Resize(, 7)) = 0
                      Next
                  End With
                  
              End Sub
              We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

              Comment

              Working...
              X