PDA

View Full Version : [SOLVED] What is equivalent to "or" in VBA?



Shaolin
04-15-2005, 07:36 AM
Someone here gave me this code to hide rows NOT containing the word "yes," OR "Yes," OR just "Y" What is in bold is what I added. Does it make sense? I don't know how to say 'or' in VBA.


Sub HideNonYes()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Rows(1).AutoFilter
ws.Rows(1).AutoFilter Field:=11, Criteria1:="=yes", Criteria2:="=Yes", Criteria3:="=Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCell
TypeVisible), ws.UsedRange.Offset(1, 0))
r.EntireRow.RowHeight = 12.75
ws.Rows(1).AutoFilter Field:=11, Criteria1:="<>yes"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCell
TypeVisible), ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub


VBA doesn't understand the code in red.

How about this bit of code below? I know in java || is equivalent to or, does it also work in VBA? Thanks in advanced


For Each cel In rngCheck
If LCase(cel.Value) <> "yes" || "Yes" || "Y" || "y"
Then cel.EntireRow.Hidden = True
If LCase(cel.Value) = Null
Then cel.EntireRow.Hidden = True
Next cel

Jacob Hilderbrand
04-15-2005, 09:00 AM
Try changing the code in red to this:


Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))


Here is an example of using Or in VBA.


If LCase(Cel.Value) <> "yes" Or LCase(Cel.Value) <> "y" Then

Since you are making the value lower case, there is not need to check Yes and yes, since Yes could not be one of the values ever.

mark007
04-15-2005, 09:08 AM
To clarify the lines in red were not understood as it should actually be 1 line. Jake joined them together using the line continuation character _.

Additionally in your particular situation you need to specify the Operator argument of the autofilter function:


ws.Rows(1).AutoFilter Field:=11, Operator:=xlOr, _
Criteria1:="=yes", Criteria2:="=Yes", Criteria3:="=Y"

That will then use or for the filter.

One point of note is that for xl2002 and below there are only 2 criteria available. Not sure if this changed in 2003 or not. Secondly I don't believe the filter is case sensitive so Criteria1 and criteria2 should be equivalent.

:)

Secondly

Jacob Hilderbrand
04-15-2005, 09:25 AM
I just checked 2003 and only 2 criteria are available.

Shaolin
04-15-2005, 10:18 AM
ok, thanks. After I fixed that problem I still get a compile error which reads:

"Expected list: list separator or )"

and the word TypeVisible is highlighted on the line


Set r = Application.Intersect(ws.Cells.SpecialCells( xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
It seems like every parenthesis that are opened are also closed. Or am I misunderstanding the error message? :banghead:

Killian
04-15-2005, 10:41 AM
I think your pasting in some spaces created when going back and forth between the VBE and the code tags. In my browser theres a few spaces in the middle of "xlCellTypeVisible". This should go across OK


Set r = Application.Intersect(ws.Cells.SpecialCells _
(xlCellTypeVisible), ws.UsedRange.Offset(1, 0))

Shaolin
04-15-2005, 11:13 AM
yup, I had a space that error is gone. Thanks!

then I got another error which reads:

Application-defined or object-defined error



ws.Rows(1).AutoFilter Field:=11, Criteria1:="=Yes", Criteria2:="=yes", _
Criteria3:="=YES", Criteria4:="=Y", Criteria5:="=y"


Based on what I searched on google, it can appear when a word is mispelled.

Still searching. In the meantime I'll just ask.

Jacob Hilderbrand
04-15-2005, 11:21 AM
You have too many Criteria, you can only have 2. Also you need to specify the Operator like Mark suggested.

Shaolin
04-15-2005, 11:36 AM
You have too many Criteria, you can only have 2. Also you need to specify the Operator like Mark suggested.

Oops, I forgot Mark. anyhow, what does "Operator:=xlOr" do?

Anyway, the compiler is still mad at the following line (still highlighted) after I only put 2 criteria

the error reads: "AutoFilter method of Range class failed"

ws.Rows(1).AutoFilter Field:=11, Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"

Jacob Hilderbrand
04-15-2005, 12:15 PM
Are there 11 fields in your Auto Filter?

xlOr means If Criteria 1 is True OR Criteria 2 is True then keep the data, otherwise filter it.

Shaolin
04-15-2005, 12:26 PM
Are there 11 fields in your Auto Filter?


I wish I can answer you, but I don't even know. How can I determine how many fields are in my auto filter?

Ken Puls
04-15-2005, 01:30 PM
FYI,

The Autofilter portion of this question has been asked in a new thread (http://www.vbaexpress.com/forum/showthread.php?t=2808)

To answer the 11 fields question, if there are 11 contiguous columns in your autofiler range, then it has 11 fields. (If you have columns A:K, but column D is entirely blank, though, it probably wouldn't... depending on how you set the autofilter up.)

Shaolin, is the "OR" portion of this thread solved? Can we mark it so? We can continue on with the autofilter portion in the other thread you started. :)

Jacob Hilderbrand
04-15-2005, 02:11 PM
Look at the drop down arrows for the Auto Filter. The fields start from the left and count up by 1 each column. So determine what column you are filtering by, then count how many fields there are to make sure you are referring to the correct field.

Shaolin
04-15-2005, 02:24 PM
yeah, DRJ. I am using column K which is 11.

mark007
04-16-2005, 02:24 AM
Could you paste the entire code you are now using?

Ken Puls
04-18-2005, 10:39 PM
Hi Shaolin,

This one got solved in the other thread as well, didn't it? Just wanted to check. I haven't marked it solved myself, in case there was still an outstanding issue.

Shaolin
04-19-2005, 06:17 AM
Hi Shaolin,

This one got solved in the other thread as well, didn't it? Just wanted to check. I haven't marked it solved myself, in case there was still an outstanding issue.

Yeah, it's solved. In the code you have to use the operator when you have more than one criterion as such, "Operator:=xlOr"

I marked it as solved. I need to remember to mark it. Sorry!

Cass
09-27-2005, 11:11 AM
Oops, I forgot Mark. anyhow, what does "Operator:=xlOr" do?

Anyway, the compiler is still mad at the following line (still highlighted) after I only put 2 criteria

the error reads: "AutoFilter method of Range class failed"


ws.Rows(1).AutoFilter Field:=11, Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"



well same problem how to use Operator:

i want sort "is greater than or equal to" and "is less than or equal to"

Worksheets("Total").Range("A1").AutoFilter field:=1, Criteria1:=date, Operator:= ???