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!![]()
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!![]()
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
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!![]()
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
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
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
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
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
Thanks AB33. It works perfectly. Have a great weekend ahead! Cheer!![]()
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks