VBA Wildcard character help

T

tawoodwa

New Member
Joined
Jan 9, 2017
Messages
17
I have this code, and it works perfect when the value is exactly when its "FFM-BLS15.01.00", but i need to adapt the code so that it catches all cells with "FFM" at the beginning, I tried using "FFM*" but apparently you cant use that wildcard character with an = operator. Can anyone please help me rewrite this so that it works that way?


Code: 
With worksheet_to_process
    Dim LR As Long, i As Long
    LR = worksheet_to_process.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
            With worksheet_to_process.Range("B" & i)
             If .Value = "FFM-BLS15.01.00" Then
             worksheet_to_process.Rows(i).Select
             Selection.Delete Shift:=xlUp
             i = i - 1
             End If
             
            End With
        Next i
    End With
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try
Code: 
.Value Like "[FFM]*" Then
 
Upvote 0
If I understand correctly, you want to delete all rows where the corresponding value in column B begins with FFM?

If so, try the following:

Code: 
Dim rng                     As Range
Dim worksheet_to_process    As Worksheet

With worksheet_to_process.Range("B:B")
    Set rng = .Find("FFM*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        Do
            rng.EntireRow.Delete shift:=xlUp
            Set rng = .Find("FFM*", LookIn:=xlValues, LookAt:=xlWhole)
        Loop While Not rng Is Nothing
    End If
End With
 
Upvote 0
Fluff said:
Try
Code: 
.Value Like "[FFM]*" Then
Click to expand...

Awesome!! this worked like a charm, Mind explaining the logic to me, I had already tried
Code: 
.Value like "FFM*"
but that wasnt working. what does that do differently
 
Last edited:
Upvote 0
MrKowz said:
If I understand correctly, you want to delete all rows where the corresponding value in column B begins with FFM?
Click to expand...
If you are correct, then here is another snippet of code (no looping) that will also work...
Code: 
With worksheet_to_process.Columns("B")
  .Replace "FFM*", "#N/A", xlPart
  On Error Resume Next
  .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End With
 
Upvote 0
tawoodwa said:
Awesome!! this worked like a charm, Mind explaining the logic to me, I had already tried
Code: 
.Value like "FFM*"
but that wasnt working. what does that do differently
Click to expand...
Using the Like operator...

"FFM*" should have worked

"[FFM]*" matches any text starting with an F or M
 
Upvote 0
tawoodwa said:
Awesome!! this worked like a charm
Click to expand...
Glad to help & thanks for the feedback
As for
Mind explaining the logic to me
Click to expand...
I wish I could, it's something I learnt from Rick Rothstein recently. Why it works with the [] but not without beats me:(
 
Upvote 0
:oops: there is a "not" missing from that. it should have read
Judging by this, my solution was not very good.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Macro to display message end of Code where Col D contains blanks
Replies
7
Views
328
howard
H
S
  • Question
Parse data - Overwrite lines with updated data
Replies
0
Views
110
Scifo
S
J
  • Question
Help with VBA Code to to unlock and lock sheet when using a command a command
Replies
1
Views
118
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
VBA code - Borders for Table
Replies
2
Views
199
Spaztic
S
S
  • Solved
Compare cell in column to cells below
2
Replies
13
Views
585
kevin9999
K

Forum statistics

Threads
1,214,527
Messages
6,120,058
Members
448,940
Latest member
mdusw

Share this page

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