Help to remove values in column 1 based on values in column b that have wildcards.

doittoday123

New Member
Joined
Sep 17, 2014
Messages
3
12017163235 1201*12023870700
12019740541 1205*12026211780
12023870700
12026211780
12052124577
12052215238

<tbody>
</tbody>

I am trying to accomplish the above. I have spend several hours working on it with no success. I'm using excel 2013.

Thanks
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi doittoday123,

Welcome to MrExcel!!

See if this is what you're after:

Code:
Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.

    Dim rngCell As Range
    Dim varMyArray As Variant, _
        varArrayItem As Variant
    Dim blnMatch As Boolean
    
    varMyArray = Array("1201*", "1205*")
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("A" & lngStartRow & ":A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each varArrayItem In varMyArray
            If CStr(rngCell) Like CStr(varArrayItem) Then
                blnMatch = True
            End If
        Next varArrayItem
        If blnMatch = False Then
            Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = rngCell
        Else
            blnMatch = False 'Reset variable
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0
Hi doittoday123,

Welcome to MrExcel!!

See if this is what you're after:

Code:
Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.

    Dim rngCell As Range
    Dim varMyArray As Variant, _
        varArrayItem As Variant
    Dim blnMatch As Boolean
    
    varMyArray = Array("1201*", "1205*")
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("A" & lngStartRow & ":A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each varArrayItem In varMyArray
            If CStr(rngCell) Like CStr(varArrayItem) Then
                blnMatch = True
            End If
        Next varArrayItem
        If blnMatch = False Then
            Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = rngCell
        Else
            blnMatch = False 'Reset variable
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Thank you, I have 60 or 70 values in column b... Should I enter them all in the line that says "varMyArray = Array("1201*", "1205*")"?
 
Upvote 0
Hi Robert,

Actually I just looked and I have over 90 values that are in column b. Is it possible to have it just look at column b to get the data? Or will I need to update the script with all the values?

Thanks,
Jim
 
Upvote 0
Hi Jim,

For that many items you'll need a dynamic array like so:

Code:
Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting data row number. Change to suit.
    
    Dim varDataMatrix() As Variant 'Declares a dynamic array variable
    Dim varDataMatrixItem As Variant
    Dim lngArrayCount As Long
    Dim rngCell As Range
    Dim blnMatch As Boolean
    
    Application.ScreenUpdating = False
    
    'Create an array of all the entries Col. B
    For Each rngCell In Range("B" & lngStartRow & ":B" & Range("B" & Rows.Count).End(xlUp).Row)
        lngArrayCount = lngArrayCount + 1
        ReDim Preserve varDataMatrix(1 To lngArrayCount) 'Append the record to the existing array
        varDataMatrix(lngArrayCount) = rngCell
    Next rngCell
    
    For Each rngCell In Range("A" & lngStartRow & ":A" & Range("A" & Rows.Count).End(xlUp).Row)
        For lngArrayCount = 1 To UBound(varDataMatrix)
            If CStr(rngCell) Like CStr(varDataMatrix(lngArrayCount)) Then
                blnMatch = True
                Exit For
            End If
        Next lngArrayCount
        If blnMatch = False Then
            Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = rngCell
        Else
            blnMatch = False 'Reset variable
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top