Results 1 to 7 of 7

restrict collection to text values only

Threaded View

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    restrict collection to text values only

    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??
    Private Sub SubContractors()
    
    Dim rng As Range
    With Sheets("Sheet1")
    Lrow = .Range("a" & Rows.Count).End(xlUp).Row
    Set rng = .Range("a2:a" & Lrow)
    
        vaData = rng
    
        Set colUnique = New Collection
        
        For i = LBound(vaData, 1) To UBound(vaData, 1)
    
            On Error Resume Next
                colUnique.Add vaData(i, 1), CStr(vaData(i, 1))
    
            On Error GoTo 0
        Next i
        ReDim aOutput(1 To colUnique.Count, 1 To 1)
        For i = 1 To colUnique.Count
            aOutput(i, 1) = colUnique.Item(i)
        Next i
        .Range("h2").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
        
        End With
    End Sub
    Attached Files Attached Files
    Last edited by nigelog; 05-24-2018 at 09:40 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] The collection of values during the filtration
    By mido21 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2017, 12:03 PM
  2. Replies: 4
    Last Post: 10-30-2015, 01:30 PM
  3. [SOLVED] Performance of iteration through Words collection degrades as collection gets bigger
    By 6StringJazzer in forum Word Programming / VBA / Macros
    Replies: 7
    Last Post: 01-14-2014, 09:44 AM
  4. Restrict Text box Text to Actual Size of Text box
    By zhb12810 in forum Excel General
    Replies: 2
    Last Post: 10-07-2012, 03:54 AM
  5. Replies: 2
    Last Post: 12-23-2011, 11:04 AM
  6. Replies: 13
    Last Post: 08-17-2011, 02:16 PM
  7. Restrict text entry in textbox to JUST TEXT
    By TheNewGuy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2009, 03:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1