Results 1 to 10 of 10

Thread: Hiding Rows

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Hiding Rows

    I have posted this on the Excel Help board, but no help. I have a worksheet that contains several columns that in turn contain cells that contain formulas that link to other worksheets. What I would like to do is hide (not delete) a row where all of the entries in the cells of that row are returning a zero or are bland or a combination of the two conditions. See attached. I want to HIDE rows 2 and 4. Any help?Thanks in advance.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.L.,

    Here's some VBA that will do the trick. The code assumes like your test data that all cells on the summary sheet contain formulas.
    Code:
    Option Explicit
    
    Sub HideRows()
    
       Dim lRowNo As Long
       Dim lColNo As Long
       Dim bHide  As Boolean
       
       [A1].End(xlDown).Select
       
       lRowNo = ActiveCell.Row()
       
       
       Do While lRowNo > 0
          lColNo = 1
          bHide = True
          
          Do
          
            If Cells(lRowNo, lColNo) <> 0 Then
              bHide = False
              Exit Do
            Else
              lColNo = lColNo + 1
            End If
            
          Loop Until Cells(lRowNo, lColNo) = ""
             
            If bHide Then Rows(lRowNo).EntireRow.Hidden = True
            lRowNo = lRowNo - 1
         
       Loop
       
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks RG. How about this one. Again, only rows 7 and 9 should be hidden (because they contain formulas), but not row 4 (i. e., no formulas.
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.L.,

    Could you elaborate?
    From what I can see the range b5:E10 are all formulas, what is different about rows 7 and 9?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RG,There is no difference-when I try and run the macro in this revised spreadsheet, it hides the row with no formulas and that is all.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.L.,

    Your data has changed so the macro has to be changed to adjust to the position of the data as follows:
    Code:
    Sub HideRows()
    
       Dim lRowNo As Long
       Dim lColNo As Long
       Dim bHide  As Boolean
       
       [A5].End(xlDown).Select  '*** Changed
       
       lRowNo = ActiveCell.Row()
       
       
       Do While lRowNo > 5   '*** Changed
          lColNo = 2         '*** Changed
          bHide = True
          
          Do
          
            If Cells(lRowNo, lColNo) <> 0 Then
              bHide = False
              Exit Do
            Else
              lColNo = lColNo + 1
            End If
            
          Loop Until Cells(lRowNo, lColNo) = ""
             
            If bHide Then Rows(lRowNo).EntireRow.Hidden = True
            lRowNo = lRowNo - 1
         
       Loop
       
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks again RG.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by jlkirk View Post
    I have posted this on the Excel Help board, but no help.
    If you mean MrExcel, did you not notice it was turned off?
    Also posted (and answered) here.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Rory,Here is the link: http://www.excelforum.com/. And yes, I posted it on Eileen's after trying to get RG's code to work, to no avail (must be something to do with being all thumbs! LOL!!!).Thanks.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Oh, you posted on EF. (I assumed from the workbook name you meant MrExcel )
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •