filtering for <> not a numer

[jordanburch]

Hey Guys,

I have the following

ActiveSheet.Range("$A$1:$AV$6000").AutoFilter Field:=6, Criteria1:=Array( _
"<>1110", "<>2220"), Operator:=xlFilterValues
basically I am trying to unclick the 1110 and 2220 values. its not working. can anyone help?

Jordan

 

[Fluff]

Changing the format to a number will not affect the actual value in the cell, just the way it's displayed.
As those values are text & not numbers, either convert them to actual numbers, or put a formula in an unused column like

=OR(F2=1110&"",F2=2220&"")

and filter that column for False.

 

[jordanburch]

Changing the format to a number will not affect the actual value in the cell, just the way it's displayed.
As those values are text & not numbers, either convert them to actual numbers, or put a formula in an unused column like

=OR(F2=1110&"",F2=2220&"")

and filter that column for False.

got it thank for the help! So theres no way to filter out them as text even?

 

[Fluff]

Not that I'm aware of, but then I always ensure my numbers are numbers & not text.

 

[Peter_SSs]

See if this does what you want.

Sub AF()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A1:AV" & Range("F" & Rows.Count).End(xlUp).Row + 1)
    a = .Columns(6).Value
    For i = 2 To UBound(a)
      Select Case a(i, 1)
        Case "1110", "2220"
        Case Else: d(a(i, 1) & "") = 1
      End Select
    Next i
    .AutoFilter Field:=6, Criteria1:=d.keys(), Operator:=xlFilterValues
  End With
End Sub

 

[jordanburch]

Not that I'm aware of, but then I always ensure my numbers are numbers & not text.

what about coding column f as real numbers. Should i just record that? or do you have a clever way thats more reliable of doing that?

See if this does what you want.

Sub AF()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A1:AV" & Range("F" & Rows.Count).End(xlUp).Row + 1)
    a = .Columns(6).Value
    For i = 2 To UBound(a)
      Select Case a(i, 1)
        Case "1110", "2220"
        Case Else: d(a(i, 1) & "") = 1
      End Select
    Next i
    .AutoFilter Field:=6, Criteria1:=d.keys(), Operator:=xlFilterValues
  End With
End Sub

Works like a charm thank you Sir I really appreciate it!

 

[Fluff]

what about coding column f as real numbers.

You can use Text to columns, select col F & text to columns (on data tab) Delimited, next, uncheck all tick boxes & finish.

 

[jordanburch]

You can use Text to columns, select col F & text to columns (on data tab) Delimited, next, uncheck all tick boxes & finish.

thanks fluff! I think that was from yesterday and I by accident quoted you. Thanks again for you help as well! Your method works great. Learning so much from you guys!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

thanks fluff! I think that was from yesterday and I by accident quoted you.

Does that mean that your question at the top of post #15 was directed at me? If so, can you expand on what you have and what you want to do?

Glad the AutoFilter code worked for you.

 

[jordanburch]

Does that mean that your question at the top of post #15 was directed at me? If so, can you expand on what you have and what you want to do?

Glad the AutoFilter code worked for you.

no Peter sorry. Im not even sure how I did that lol. Your code worked great thanks again!