Remove entire row based on a text that starts with a specific letter (beginner) vba macro

[alksndr]

Hi,

how can I achieve deleting the entire row based on a text starting with letter "X"

actually, I already have the code below from my previous inquiry and I just forgot to include this particular requirement. is there any way to embed the missing function?

sub duplicate()

Dim Rng As Range, Dn As Range, n As Long
Dim Lst As Long, nRng As Range
Lst = Range("B" & Rows.Count).End(xlUp).Row
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
For n = Lst To 1 Step -1
    If Not .Exists(Range("B" & n).Value) Then
        .Add Range("B" & n).Value, Nothing
    Else
        If nRng Is Nothing Then
            Set nRng = Range("B" & n)
        Else
            Set nRng = Union(nRng, Range("B" & n))
        End If
End If
Next n

If Not nRng Is Nothing Then nRng.EntireRow.Delete
End With



End Sub

Here's what my worksheet looks like:

Row     Column A   Column B     Column C      Column D 
  1       PHMBIA      Maricel A.     Chemical      ---                
  2       PHMBIA      Maricel A.     Chemical      --- 
  3       PHMBIA      Maricel A.     Chemical      9/17/2015 
  4       PHMABC     Mari L.          Refinery       --- 
  5       PHMABC     Mari L.          Refinery       --- 
  6       PHMABC     Mari L.          Refinery       --- 
  7       PHMABC     Mari L.          Refinery       ---  
  8       XHAMAO     Mall A.          Sup             ---  
  9       XHAMAO     Mall A.          Sup             ---  
10       XHAMAO     Mall A.          Sup             9/17/2015

And here's how I expect it to be after executing the macro.

Row     Column A   Column B     Column C      Column D 
  3       PHMBIA      Maricel A.     Chemical      9/17/2015 
  7       PHMABC     Mari L.          Refinery       ---

Thanks a lot for the help!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27Jun49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
Lst = Range("B" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Not .Exists(Range("B" & n).Value) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Left(Range("B" & n).Value, 1) = "X" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Range("B" & n)
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Range("B" & n))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
        .Add Range("B" & n).Value, Nothing
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Range("B" & n)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Range("B" & n))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick