Using Wildcards

[JBAR]

Hello,

How would I use a wildcard to select all the following cells except the ones with "R5" in them (have to use wildcard):

<TABLE style="WIDTH: 97pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=129 border=0><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 5504" width=129><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 97pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=129 height=17>D9A-C R1/R2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A-C R1/R2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A-D R4-500HP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A-D R4-500HP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A-C R1/R2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A MG HE/KC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A MG HE/KC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A MG HE/KC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A - R5-575</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>D9A - R5-575HP</TD></TR></TBODY></TABLE>

I've tried *[!R5]* and *[!R][!5]* but these don't seem to work.

Thanks,

JBAR

 

[iliace]

If you actually mean Select, I would use AutoFilter, where you can specify a wildcard (does not contain). Then, highlight the range, press F5 (Go To), click Special... and choose Visible Cells.

 

[Norie]

JBAR

Use a wildcard where? In a filter? In a formula?

 

[JBAR]

This is in a VB application that analyzes alot of data. The only way it this application allows a filter is by wildcards.

-JBAR

 

[Norie]

Sorry you've lost me.

What VB application are you referring to?

 

[RomulusMilea]

Hello,

On top of your data create a header of this column (any name you want), and then press CTRL + B, to bold it. Then choose Data -> Filter -> Autofilter -> OK. Use filter arrow, choose Custom filter, then pick the option "Does not contain", in the right field just write R5. In the end, press OK. I hope this helps.

Romulus.

 

[VoG]

If you mean in code and assuming that the values are in column A

Sub wc()
Dim LR As Long, i As Long, j As Long, rng As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Not Range("A" & i).Value Like "*R5*" Then
        Set rng = Range("A" & i)
        Exit For
    End If
Next i
For j = i + 1 To LR
    If Not Range("A" & j).Value Like "*R5*" Then
        Set rng = Union(rng, Range("A" & j))
    End If
Next j
rng.Select
End Sub

but it should not be necessary to Select this range in order to work with it.

 

[JBAR]

This is a specialized excel VBA program made for my company that activates a userform. The userform allows for certain columns to be filtered, but only using wildcards. After the userform is closed, the data is analyzed in a certain way in which it needs both the complete data set and the filtered data. I've used wildcards in the past to filter data correctly. This filter seems more complicated. Does anybody have an idea how to filter this using wildcards?

Thanks,

JBar

 

[Norie]

JBar

If this is a bespoke application then I think you'll need to speak to the creator.

How are we to know how they are actually doing the filtering?

PS Why can you only use wildcards?