excel vba force array elements to alphanumeric
'VBA function (extremely fast) to force an array column
'to ONLY alpha-numeric characters (with spaces):
Function ForceArrayColumnAlphaNumeric(v, Optional col& = 1)
Dim i&, j&, p&, max&, t&
Dim b() As Byte, res() As Byte, Keep(0 To 255) As Boolean
Const VALS$ = "0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
For j = 1 To Len(VALS)
Keep(Asc(Mid$(VALS, j, 1))) = 1
Next
For i = LBound(v) To UBound(v)
p = 0
max = Len(v(i, col))
ReDim res(0 To max)
b = StrConv(v(i, col), vbFromUnicode)
For j = 0 To max - 1
t = b(j)
If Keep(t) Then
res(p) = t
p = p + 1
End If
Next
v(i, col) = StrConv(res, vbUnicode)
Next
End Function
'----------------------------------------------------------------------------------
array = [A1:Z999].Value2
ForceArrayColumnAlphaNumeric array, 3 '<--the 3rd column of array is now cleansed
'NB: Adjust the VALS$ constant to include only the characters you want to keep.
'NB: Array must be 2D. The column cleansed defaults to column 1.
Are there any code examples left?
New code examples in category VBA
-
VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
-
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
-
VBA 2022-03-27 17:05:52 vba simple quote
-
VBA 2022-03-27 14:30:19 textbox find and replace vb
-
VBA 2022-03-27 09:10:21 nested if else in vb.net
-
VBA 2022-03-27 09:10:09 excel vba string to bits
-
VBA 2022-03-25 02:00:15 vba verticalalignment
-
VBA 2022-03-24 12:20:06 Excel web scraping
-
VBA 2022-03-24 10:11:00 excel vba BitToLong
-
VBA 2022-03-21 18:00:05 excel vba save file txt