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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,642
Office Version
  1. 365
Platform
  1. Windows
A few things to check:

Are the entries in column O numeric (numbers), or are they text entered as numbers?
One way to check is to identify a cell that meets your condition, and enter this formula into any blank cell and see what it returns.
(let's say it is cell O4):
VBA Code:
=ISNUMBER(O4)
If that returns TRUE, then check this formula:
Excel Formula:
=O4<0
Also note. In your original question, you said <=0, but in your code, you were using <0.
So which one is it that you really need?

The second thing to check is to see if column P is EXACTLY equal to "No". Is it always written like that, or might it also be "NO" or "no"?
Also make sure tha there are no extra spaces at the end of it.
Just like above, find an entry in column that meets your condition, and enter this formula in a blank cell and see what it returns.
(let's say it is cell P4):
=P4="No"

Lastly, make sure BOTH those conditions are met on the same row, for the row to be hidden.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

excel_dn

New Member
Joined
Oct 14, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I can confirm that in Col O it is numeric numbers and in Col P it is text.

In Col P if a cell says No it will always appear as "No"

I need it to be LESS THAN zero which is why I took out the "="

:(
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,642
Office Version
  1. 365
Platform
  1. Windows
Did you run all those tests that I gave you, and confirmed each formula returned TRUE?

Also, do you have any blanks in column A in your data? Because the macro will stop at the first blank it finds in column A.

If neither of those is the issue, please post a sample of your data. You can do that using the tool mentioned here: XL2BB - Excel Range to BBCode
 

excel_dn

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

I had blanks, got rid of them, thanks so much it works now!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,642
Office Version
  1. 365
Platform
  1. Windows
I had blanks, got rid of them, thanks so much it works now!
Excellent! Glad to hear it!

Note that you can also alter that check to look at a different column, if it is more reliable to use a column other than A to find identify which rows to run against.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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