title: How to create a validation list for range exceeding 255 characters
body: <p>Initially I did not know why my Excel file kept getting corrupted but after much research, I realised that it's because my Data Validation drop-down list has more than 255 characters. "A comma separated data validation list that exceeds 255 characters (including commas) will corrupt a workbook" </p>
  
  <p>This was my code:</p>
  
  <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  If Not Intersect(Target, Range("E12:E21")) Is Nothing Then
  
  Dim col As New Collection
  Dim rng As Range
  Dim i As Long
  Dim dvlist As String
  
  'Loop thru the data range i.e. these are the cells used for the validation list. Very long list.
  For Each rng In Sheet2.Range("B2:B249 , D2:D19")
  'ignore blanks
      If Len(Trim(rng.Value)) &lt;&gt; 0 Then
          'create a unique list
          On Error Resume Next
          col.Add rng.Value, CStr(rng.Value)
          On Error GoTo 0
      End If
  Next rng
  
  'concatenate with "," as the delimiter
  For i = 1 To col.Count
      dvlist = dvlist &amp; col.Item(i) &amp; ","
  Next i
  
  'add it to the DV
  With Sheet1.Range("E12:E21").Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Formula1:=dvlist
  End With
  
  End If
  
  
  If Not Intersect(Target, Range("F12:F21")) Is Nothing Then
  
  'Loop thru the data range i.e. these are the cells used for the validation list. Very long list.
  For Each rng In Sheet2.Range("A2:A249")
  'ignore blanks
      If Len(Trim(rng.Value)) &lt;&gt; 0 Then
          'create a unique list
          On Error Resume Next
          col.Add rng.Value, CStr(rng.Value)
          On Error GoTo 0
      End If
  Next rng
  
  'concatenate with "," as the delimiter
  For i = 1 To col.Count
      dvlist1 = dvlist1 &amp; col.Item(i) &amp; ","
  Next i
  
  'add it to the DV
  With Sheet1.Range("F12:F21").Validation
      .Delete
      .Add Type:=xlValidateList, _
      AlertStyle:=xlValidAlertStop, _
      Formula1:=dvlist1
  End With
  
  
  End If
  
  
  End Sub
  </code></pre>
  
  <p>As you can see, my list is very long and it has spaces in between the list that because of reasons, cannot be removed. Hence, I am unable to use data validation function in Excel. Is there any way I can go around this issue?</p>