title: Get Text from Range - VBA (Excel)
body: <p>I want to make a data validation list from text only in a Range of cells.</p>
  
  <p>I searched for formula but I found nothing so I decided to make my own function but its not working.</p>
  
  <p>I tried those codes:</p>
  
  <p>Code 1:</p>
  
  <pre><code>Function ListFromRange(rng As Range) As Range
  
      Dim cl As Range
      Dim entry As Range
  
      For Each cl In rng
          If Not IsNumeric(cl.Value) Or cl.Value = "" Then
              If entry Is Nothing Then
                  Set entry = cl
              Else
                  Set entry = Union(entry, cl)
              End If
          End If
      Next
  
      Set ListFromRange = entry
  End Function
  </code></pre>
  
  <p>Code 2:</p>
  
  <pre><code>Function ListFromRange2(rng As Range) As Variant
  
      Dim cl As Range
      Dim i As Integer
      Dim entry() As String
      ReDim entry(rng.Count)
  
      For Each cl In rng
          If Not IsNumeric(cl.Value) Or cl.Value = "" Then
              entry(i) = cl.Value
              i = i + 1
          End If
      Next
  
      ListFromRange2 = entry
  End Function
  </code></pre>
  
  <p>The second code is working but when I use with a defined name and use that defined name for data validation list its tells me that there is an error in the validation list source but when I use this function with index its returning the desired result.</p>
  
  <p>Also some images to explain more:</p>
  
  <p><a href="https://i.stack.imgur.com/pKZW9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pKZW9.png" alt="Source Range"></a></p>
  
  <p>I want to make a list from cells that contains a text and apply it here:</p>
  
  <p><a href="https://i.stack.imgur.com/t0Zqg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t0Zqg.png" alt="Target List"></a></p>
  
  <p>But without the number values.</p>