VBA to Hide Rows

[excel_dn]

Hi Guys,

I'm looking for a VBA that will hide rows when two conditions are met.

In column O I have a numeric value which, for a row to be hidden needs to be less than 0.

In column P I have either Yes/No. When it is No I want the row to be hidden.

Therefore when O = <0 AND P = No then row will be hidden.

TIA

 

[Joe4]

Welcome to the Board!

The following shows you a VBA and a non-VBA way how you can accomplish this:

Hiding Rows Based on Two Values (Microsoft Excel)

It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two cells? Here are a couple of ideas on how you can pare down your data. (Tips.Net)

excel.tips.net

 

[excel_dn]

Hi Joe4,

I tried to edit the macro but no luck?

Are you able to help further please

 

[Joe4]

Please post your code attempt, and we will see if we can help you clean it up.

 

[excel_dn]

Sub Hide()
Dim Criteria as Boolean
Dim i As Integer

i = 1
Do Until Trim(Cells(i, 1).Value) = ""
Criteria = True
Criteria = Criteria And (Cells(i, 15).Value < 0) _
And Cells(i, 2).Value <> ""
Criteria = Criteria And (Cells(i, 16).Value = "No") _
And Cells(i, 3).Value <> ""
If Criteria Then Rows(i).EntireRow.Hidden = True
i = i + 1
Loop
End Sub

 

[Joe4]

Note these two parts of the code:

And Cells(i, 2).Value <> ""

and

And Cells(i, 3).Value <> ""

These are checking/verifying that columns B and C are not blank.
Is this pertinent to what you are trying to do? If not, you may need to remove those parts of the code.

 

[excel_dn]

Thanks. I removed them and now I get a syntax error when trying to run

Sub Hide()
Dim Criteria As Boolean
Dim i As Integer

i = 1
Do Until Trim(Cells(i, 1).Value) = ""
Criteria = True
Criteria = Criteria And (Cells(i, 15).Value < 0) _
Criteria = Criteria And (Cells(i, 16).Value = "No") _
If Criteria Then Rows(i).EntireRow.Hidden = True
i = i + 1
Loop
End Sub

with the below section in red when I try to run
" Criteria = Criteria And (Cells(i, 15).Value < 0) _
Criteria = Criteria And (Cells(i, 16).Value = "No") _
If Criteria Then Rows(i).EntireRow.Hidden = True "

I just need to check columns O and P to see if it is less than "0" AND "No" and if those conditions are met, the row needs to be hidden

 

[Joe4]

OK, the parts that you removed were continuations of the previous line. If you notice, the previous lines ended in a "_", which indicate that it wraps to the next line. So if you remove those pieces, you need to remove the "_" too, i.e.

Sub Hide()

    Dim Criteria As Boolean
    Dim i As Integer

    i = 1
    Do Until Trim(Cells(i, 1).Value) = ""
        Criteria = True
        Criteria = Criteria And (Cells(i, 15).Value < 0)
        Criteria = Criteria And (Cells(i, 16).Value = "No")
        If Criteria Then Rows(i).EntireRow.Hidden = True
        i = i + 1
    Loop
    
End Sub

 

[Joe4]

Also note, that I think in that link he broke everything out explicitly just to make it easier to see what is happening. But you can actually simplify all that to this:

Sub Hide()

    Dim i As Integer

    i = 1
    Do Until Trim(Cells(i, 1).Value) = ""
        If (Cells(i, 15).Value < 0) And (Cells(i, 16).Value = "No") Then Rows(i).EntireRow.Hidden = True
        i = i + 1
    Loop

End Sub

 

[excel_dn]

Hi,

Thanks, I have run that last one you posted. It returns no error, but doesn't hide any rows?

Am I missing something?