VBA Code (Hide Rows)

[Range]

Hi,

I need some VBA code to hide some rows depending on a ">0" criteria in column P.

Thanks

 

[taurean]

Try:

Public Sub HideRows()
Dim r As Range
Dim lLastRow As Long
lLastRow = Range("P" & Rows.Count).End(xlUp).Row
For i = 1 To lLastRow
    If Range("P" & i).Value > 0 Then
        If r Is Nothing Then
        Set r = Range("P" & i)
        Else
        Set r = Union(r, Range("P" & i))
        End If
    End If
Next i
If Not r Is Nothing Then r.EntireRow.Hidden = True
End Sub

 

[Range]

Ahh brilliant, thanks very much. But one more thing... IF I wanted to do it when cell = 0 how would I ensure that the cells without a sum in there are not hidden?

Would it be better to go with a "true" criteria instead?

 

[Michael M]

try

Public Sub HideRows()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "P").End(xlUp).Row
For i = lr To 1 Step -1
    If Range("P" & i).Value >= 0 Then
    Rows(i).EntireRow.Hidden = True
    End If
Next i
End Sub

 

[Yahya]

you can use Filter by code
if your data from A
try this code

Sub Test()
Dim lR As Long
lR = Range("P" & Rows.Count).End(xlUp).Row
With Range("A1:P" & lR)
    .AutoFilter 16, ">0"
End With
End Sub

 

[taurean]

Ahh brilliant, thanks very much. But one more thing... IF I wanted to do it when cell = 0 how would I ensure that the cells without a sum in there are not hidden?

Would it be better to go with a "true" criteria instead?

Sorry, I didn't understand. Does it mean:
"All rows with column P values equal to zero shall be hidden except where a sum formula is used and it is showing zero."?

 

[Range]

Sorry Guys,

I only want this code to hide rows 13:31....in column P.... what would I change? At the moment it hides alot of info that i still need

Sorry that I keep changing my mind!!

 

[Yahya]

Range, try this just to hide rows from 13 to 31

Sub Test()
Rows("13:31").Hidden = True
End Sub

 

[Range]

Sorry, I still need the criteria sum=0 to be the reason why the row is hidden!!

 

[Yahya]

Sorry, I still need the criteria sum=0 to be the reason why the row is hidden!!

same with Michael M Code

Public Sub HideRows()
Dim i As Integer
For i = 31 To 13 Step -1
    If Range("P" & i).Value >= 0 Then
    Rows(i).EntireRow.Hidden = True
    End If
Next i
End Sub