英文:
Autofilter values contains the Asterisk (*)
问题
On Excel, I can use tilde (~) before Asterisk (*) to filter values containing the asterisk (*) and it works.
I tried the same on VBA, but it didn't work at all (the result is null values).
I have many values containing the asterisk (*) as a string like (8*16, 10*11).
Kindly, how to fix this issue without replacing the asterisk on my main dataset.
Option Explicit
Option Compare Text
Sub AutoFilter_on_Asterisk()
Dim ws As Worksheet, rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("A3:AH7000")
rng.AutoFilter Field:=5, Criteria1:="~*", Operator:=xlFilterValues 'the result is null values
End Sub
英文:
On Excel, I can use tilde (~) before Asterisk (*) to filter of values contains the asterisk (*) and it works.
I tried the same on VBA, But It didn't work at all (the result is null values).
I have many values contains the asterisk (*) as a string like ( 8*16 , 10*11).
kindly , how to fix this issue without replacing the asterisk on my main dataset.
Option Explicit
Option Compare Text
Sub AutoFilter_on_Asterisk()
Dim ws As Worksheet, rng As Range
Set ws = ActiveSheet
Set rng = ws.Range("A3:AH7000")
rng.AutoFilter field:=5, Criteria1:="~*", Operator:=xlFilterValues 'the result is null values
End Sub
答案1
得分: 3
"波浪号"转义了星号字符，表示您不想将其用作通配符，而是字符。如果您将X输入为条件，Excel将显示所有完全匹配 X 的行。如果要获取值包含 X 的所有行，可以在搜索字段中输入*X*，或者使用“文本筛选->包含...”对话框（执行相同的搜索，只是您不需要知道星号是通配符字符）。
搜索星号的方式完全相同：您的代码搜索包含仅星号的行（因为您没有任何星号，所以结果为空）。您的搜索文本应为*~**：
rng.AutoFilter field:=5, Criteria1:=*~**, Operator:=xlFilterValues
英文:
The tilde "escapes" the asterix character to signal that you don't want to use it as wildcard, but as character. If you enter X as criteria, Excel will show you all rows that are exactly X. If you want to get all rows where the value contains an X, you can either enter *X* in the search field, or you use the "TextFilters->Contains..." dialog (which will do the same search, only that you don't need to know that the asterix is the wildcard character).
Searching for an asterix is exactly the same: Your code searches for rows that contain only an asterix (and as you don't have any, your result is empty). Your search text needs to be *~**:
rng.AutoFilter field:=5, Criteria1:="*~**", Operator:=xlFilterValues
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论