+ Reply to Thread
Results 1 to 10 of 10

Macro to hide (or group) rows if value if zero

Hybrid View

bernard.x Macro to hide (or group) rows... 09-26-2012, 06:17 AM
AB33 Re: Macro to hide (or group)... 09-26-2012, 08:04 AM
bernard.x Re: Macro to hide (or group)... 09-26-2012, 10:34 PM
AB33 Re: Macro to hide (or group)... 09-27-2012, 06:04 AM
AB33 Re: Macro to hide (or group)... 09-27-2012, 06:07 AM
AB33 Re: Macro to hide (or group)... 09-27-2012, 07:34 AM
AB33 Re: Macro to hide (or group)... 09-27-2012, 07:11 AM
AB33 Re: Macro to hide (or group)... 09-27-2012, 02:59 PM
bernard.x Re: Macro to hide (or group)... 09-28-2012, 02:11 AM
AB33 Re: Macro to hide (or group)... 09-28-2012, 03:00 AM
  1. #1
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    29

    Macro to hide (or group) rows if value if zero

    I need a macro to hide (or to group and collapse) the rows that has zero value in two particular columns adjunct to the row. Please see the attachment for details. Appreciate your kind assistance. Cheers!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    Trt this one
    Sub HideZeros()
    
    Dim WS As Worksheet, rngCell As Range, rngSource As Range, LR As Long
    
    Set WS = ActiveWorkbook.Sheets("Details")
    
       With Application
          .ScreenUpdating = 0
          .EnableEvents = 0
       End With
    
          With WS
             LR = .Cells(.Rows.Count, "D").End(xlUp).Row
             Set rngSource = WS.Range(Cells(6, "D"), Cells(LR, "F"))
          End With
    
          For Each rngCell In rngSource
             If rngCell.Value = "" Or rngCell.Value = 0 Then
                rngCell.EntireRow.Hidden = True
             End If
          Next
    
       With Application
          .EnableEvents = 1
          .ScreenUpdating = 1
       End With
       End Sub

  3. #3
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Macro to hide (or group) rows if value if zero

    Thanks for the reply, AB33! This macro seems to hide rows that has value in column F as well. I need to hide rows that have zero value in both column D & F. Can you assist please? Thanks a million! Cheers!

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    Bernard!
    I have tried this code, but it does not seem to work. Perhaps, more able people could spot my error

    Sub HideZeros()
    
    Dim WS As Worksheet, c As Range, LR As Long
    
    Set WS = ActiveWorkbook.Sheets("Details")
    
       With Application
          .ScreenUpdating = 0
          .EnableEvents = 0
       End With
    
          With WS
           LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
         
        For I = 6 To LR
        
            If .Range("D6:D" & I).value And .Range("F6:F" & I).value = "" Or .Range("D6:D" & I).value And .Range("F6:F" & I).value = 0 Then
            
        
               EntireRow.Hidden = True
             
            End If
        Next
     End With
       With Application
          .EnableEvents = 1
          .ScreenUpdating = 1
       End With
       End Sub

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    Bernard!

    I have tried this code, but it does not seem to work. Perhaps, more able people could spot my error.

    Sub HideZeros()
    
    Dim WS As Worksheet, c As Range, LR As Long
    
    Set WS = ActiveWorkbook.Sheets("Details")
    
       With Application
          .ScreenUpdating = 0
          .EnableEvents = 0
       End With
    
          With WS
           LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
         
        For I = 6 To LR
        
            If .Range("D6:D" & I).value And .Range("F6:F" & I).value = "" Or .Range("D6:D" & I).value And .Range("F6:F" & I).value = 0 Then
            
        
               EntireRow.Hidden = True
             
            End If
        Next
     End With
       With Application
          .EnableEvents = 1
          .ScreenUpdating = 1
       End With
       End Sub

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    This one seems to work

    Sub Hide()
        Dim Criteria As Boolean, i As Long
        On Error Resume Next
    
        i = 6
        Do Until Trim(Cells(i, 1).Value) = ""
            Criteria = True
            Criteria = Criteria And (Cells(i, 4).Value = "") And Cells(i, 6).Value = "" _
            Or Criteria And (Cells(i, 4).Value = 0) And Cells(i, 6).Value = 0
            If Criteria Then Rows(i).EntireRow.Hidden = True
            i = i + 1
        Loop
    End Sub

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    I have got this one from on-line and adjusted it to your needs. See if this one works

    Sub Hide()
        Dim Criteria As Boolean, i As Long
    
        i = 6
        Do Until Trim(Cells(i, 3).Value) = ""
            Criteria = True
            Criteria = Criteria And (Cells(i, 4).Value = "") And Cells(i, 6).Value = ""
            If Criteria Then Rows(i).EntireRow.Hidden = True
            i = i + 1
        Loop
    End Sub

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    I have now corrected my earlier code and this one also seems to work.
    Sub HideZeros()
    
    Dim WS As Worksheet, c As Range, LR As Long
    
    Set WS = ActiveWorkbook.Sheets("Details")
    On Error Resume Next
    
       With Application
          .ScreenUpdating = 0
          .EnableEvents = 0
       End With
    
          With WS
           LR = .Cells.Find("*", .Cells(Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
         
        For i = 6 To LR
        
            If .Range("D" & i).Value = "" And .Range("F" & i).Value = "" _
            Or .Range("D" & i).Value = 0 And .Range("F" & i).Value = 0 Then
            
        
          Rows(i).EntireRow.Hidden = True
             
            End If
        Next
     End With
       With Application
          .EnableEvents = 1
          .ScreenUpdating = 1
       End With
       End Sub

  9. #9
    Registered User
    Join Date
    11-03-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Macro to hide (or group) rows if value if zero

    Thanks AB33. It works perfectly. Have a great weekend ahead! Cheer!

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro to hide (or group) rows if value if zero

    Glad to know it worked for you!
    Like you, I am a learner, picking up few things as I go and hopefully to get better by a day. The moderators may ask you to close this thread if you are happy with the solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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