restrict collection to text values only

[nigelog]

I have a list of entries that contain many instances of both numeric and text value that I am trying to restrict to text only (I dont need and items beginning with a number). This code gives me an output in column H of unique entries.

How can I use an If statement to filter only the entries starting with a letter??

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

[CK76]

Something like...

Please Login or Register  to view this content.

[nigelog]

Thanks Norie and CK76

@Norie - spot on as usual, thanks

@ CK76, unfortunately brought in other entries containing a letter

[CK76]

Yes, didn't notice those items that didn't have " - " in string that started with numeric.

[Marc L]

Another way :

Sub Demo1()
            Dim V
    With Sheet1
            .Cells(8).CurrentRegion.Offset(1).Clear
            Application.ScreenUpdating = False
        For Each V In .Range("A2", .Cells(1).End(xlDown)).Value
            If V Like "[A-Z]*" Then
                With .Cells(8).CurrentRegion
                    If IsError(Application.Match(V, .Cells, 0)) Then .Cells(.Rows.Count + 1, 1).Value = V
                End With
            End If
        Next
    End With
            Application.ScreenUpdating = True
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Marc L]

Or via an easy advanced filter :

Sub Demo2()
    Dim V
    With Sheet1
        V = .Cells(8).Value
        .Cells(1).Copy .Cells(8)
        .[C2].Formula = "=ISERR(VALUE(LEFT(A2)))"
        .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[C1:C2], .Cells(8), True
        .Cells(8).Value = V
    End With
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !