VBA to Hide Rows

excel_dn

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

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

excel_dn

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Joe4,

I tried to edit the macro but no luck?

Are you able to help further please
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Please post your code attempt, and we will see if we can help you clean it up.
 

excel_dn

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
Note these two parts of the code:
VBA Code:
And Cells(i, 2).Value <> ""
and
VBA Code:
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

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi,

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

Am I missing something?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,573
Messages
5,625,586
Members
416,120
Latest member
B3nj1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top