英文:
excel autofilter where criteria is the cell value must not contain a letter or a number
问题
我有一个范围从A到N，最初我通过H列（字段8）进行筛选，然后我想再次通过N列（字段14）进行筛选，筛选条件是列N中的行不能包含字母或数字，应该如何操作呢？
rng.AutoFilter Field:=8, Criteria1:="Value" ' 通过H列进行筛选
rng.AutoFilter Field:=14, Criteria1:="=*[^0-9A-Za-z]*" ' 通过N列进行筛选，排除包含字母或数字的行
英文:
i have a range A to N, initially i filter it by H(field 8) then I want to filter it again by column N(field 14) with the criteria that the rows in column N must not contain a letter or a number, how would be the way to do that?
rng.AutoFilter Field:=8, Criteria1:="Value" 'Filter by Column H
rng.AutoFilter Field:=14, Criteria1:= 'Filter by Column N
答案1
得分: 2
需要创建另一列并使用此公式进行筛选：
Function hasDigitsOrNumbers(s As String) As String
Dim ch As String, ln As Long
Dim i As Long
ln = Len(s)
For i = 1 To ln
ch = Mid(s, i, 1)
If (ch >= "0" And ch <= "9") Or (ch >= "a" And ch <= "z") Or (ch >= "A" And ch <= "Z") Then
hasDigitsOrNumbers = True
Exit Function
End If
Next
hasDigitsOrNumbers = False
End Function
=hasDigitsOrNumbers(N1) 并在N1包含数字或字母时返回True，否则返回FALSE。
根据VBasic2008的评论，我编写了一个新的函数，速度大约快2.5倍：
Function hasDigitsOrNumbers(s As String) As String
hasDigitsOrNumbers = (s Like "*#*") Or (s Like "*[a-z]*") Or (s Like "*[A-Z]*")
End Function
英文:
Have to create another column with this formula and filter this column:
Function hasDigitsOrNumbers(s As String) As String
Dim ch As String, ln As Long
Dim i As Long
ln = Len(s)
For i = 1 To ln
ch = Mid(s, i, 1)
If (ch >= "0" And ch <= "9") Or (ch >= "a" And ch <= "z") Or (ch >= "A" And ch <= "Z") Then
hasDigitsOrNumbers = True
Exit Function
End If
Next
hasDigitsOrNumbers = False
End Function
=hasDigitsOrNumbers(N1) and returns true if the N1 has digits or letters, otherwise returns FALSE.
Reading VBasic2008's comment I wrote a new function which is about 2.5 times faster:
Function hasDigitsOrNumbers(s As String) As String
hasDigitsOrNumbers = (s Like "*#*") Or (s Like "*[a-z]*") Or (s Like "*[A-Z]*")
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论