Unfilter exceptions

[nvdp2002]

Dear excel boffins,

I have a macro that filters some rows out based on a cell being less than zero.
99% of the time, that's fine.
There is on occasion a reason to unhide 1 or 2 of the hidden ones. Ill explain in more detail below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]CompanyX[/TD]
[TD]Xcode[/TD]
[TD]1[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]CompanyY[/TD]
[TD]Ycode[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CompanyY[/TD]
[TD]Ycode[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CompanyZ[/TD]
[TD]Zcode[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The macro filters out row 3 at the moment because it filters out rows with 0 or less in them. What i would like to do instead is to take to total of columns 3 and 4 and filter on that.
The end result would be that if the total of columns 3 and 4 are more than 0 then it will not get filtered out.

Thanks in advance for help.

Kind Regards,
Nasser

 

[Yongle]

Use a "helper" column. Place a formula in that column, filter on that instead

So assuming above is columns A to D and that E is to contain helper formula...

manually Number Filter values in column E > 0 with this formula in E2 copied down
=C2+D2

VBA

rng.AutoFilter Field:=5, Criteria1:=">0"

 

[nvdp2002]

Hi Yongle,

Thanks for the tip. I wanted to do that as well, but the other filters refer to column numbers.
... Although after having said that i could just put the column right at the end.

Thanks for that. Talking things through clears them up nicely sometimes.

Do you know if there is another way to do with code? As in without the helper column. you idea is the easiest i think, just curious.

Kind Regards,
Nasser

 

[Yongle]

If all you want to do is to hide the rows, here is a way to do it without using data filter
- assumes first header is in A1 and data starts on row 2
- code hides all rows where columns C & D add up to zero or less

Sub HideRows()
    Dim arr, a, mystr As String
    With ActiveSheet
        arr = .Range("A1").CurrentRegion
        For a = 2 To UBound(arr)
            If arr(a, 3) + arr(a, 4) <= 0 Then
                If mystr = "" Then mystr = "A" & a Else mystr = mystr & ",A" & a
            End If
        Next a
        .Range(mystr).EntireRow.Hidden = True
    End With
End Sub

and to make visible again

Sub UnhideRows()
    ActiveSheet.Range("A1").CurrentRegion.EntireRow.Hidden = False
End Sub

The code
- copies the entire range into an array
- adds together the 3rd and 4th column in the array
- builds a comma delimited string of cell references for items that add up to 0 or less
- hides the rows containing those cells