Is it possible to filter on uppercase in Excel 2007? 2

[GoreFish]

I am trying to filter a column based on the cell value being in all upper case. The example is best illustrated below.
My data:

A B C
1 abc
2 Def
3 GHI
4 jkl
5 Mno
6 PQR

What I want to do is have the two upper case entries filtered in and the rest out (GHI and PQR in my example).

I read in an earlier post from 2008 that the best way was to use the following macro:

-----------------------------------------------
Function ContainsLower(pValue) As Boolean

Dim LLength As Integer
Dim LPos As Integer

'Check for null value
If IsNull(pValue) = False Then

'Find length of string
LLength = Len(pValue)

LPos = 1

While LPos <= LLength
'Check if value is between 'a' to 'z'
If Asc(Mid(pValue, LPos, 1)) >= 97 And Asc(Mid(pValue, LPos, 1)) <= 122 Then
ContainsLower = True
Exit Function
End If

LPos = LPos + 1
Wend

End If

ContainsLower = False

End Function
--------------------------------------------------
Following that, I was to code the following into the column B cells:

A B C
1 abc =islower(B1)
2 Def =islower(B2)
3 GHI =islower(B3)
4 jkl =islower(B4)
5 Mno =islower(B5)
6 PQR =islower(B6)

Which was supposed to yield the results below (which is a breeze to sort):

A B C
1 abc TRUE
2 Def TRUE
3 GHI FALSE
4 jkl TRUE
5 Mno TRUE
6 PQR FALSE

The problem is it is not working. I get the dreaded #NAME? with no hints at what might be wrong.

Thanks for any help or other options!

 

[SkipVought]

Put your function in a MODULE, NOT in the worksheet object code window.

Please post future VBA Questions in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[strongm]

Try the following formula in B1 to avoid having to use any VBA

=NOT(EXACT(UPPER(A1),A1))

 

[SkipVought]

also, modified your code to be more standard...

Function ContainsLower(pValue) As Boolean

     Dim LLength As Integer
     Dim LPos As Integer
[b]
     ContainsLower = False[/b]
     'Check for null value
     [b]
     If pValue <> "" Then
[/b]
          'Find length of string
          LLength = Len(pValue)
[b]
          For LPos = 1 To LLength[/b]
               'Check if value is between 'a' to 'z'
               If Asc(Mid(pValue, LPos, 1)) >= 97 And Asc(Mid(pValue, LPos, 1)) <= 122 Then
                    ContainsLower = True[b]
                    Exit For[/b]
               End If
[b]
          Next
[/b]
     End If


End Function

Invalid use of Null (Error 94)

Null is a Variant subtype used to indicate that a data item contains no valid data. This error has the following cause and solution:



Null
A value indicating that a variable contains no valid data. Null is the result of an explicit assignment of Null to a variable or any operation between expressions that contain Null.

A ZERO length string is not technically NULL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GoreFish]

You guys are amazing! Both solutions did the trick and I learned something about putting funtions in a module to boot. I can't thank you enough StrongM and SkipVought! You're my heroes!

SOLVED