英文:
How do I filter to allow only one variable AND blanks?
问题
I am trying to filter out all results on Column 5 except for one called "Pending". Often, there are no "Pending" variables on the report, so I should be left with only blanks and column headers. Every time I run the code though, it returns a single result showing a variable called "Inactive" which should be filtered out. All the other "Active" and "Inactive" results are filtered out, but it seems to think that SOMETHING has to display since it can't all be blank.
I read that sometimes you can use "=" to represent blanks in the autofilter, but it doesn't appear to work.
How do I tell VBA that I'm okay if the variable I am looking for doesn't exist on this report and it's okay to display blank results?
Current code:
Sub PepperH()
Worksheets("Sheet2").Select
Range("A9:L3000").AutoFilter Field:=5, Criteria1:="Pending", Operator:=xlOr, Criteria2:="="
'Should only show pending accounts- headers run A1-A8 so we need to start after that
End Sub
End result of that code is the report looks like this: vast majority filtered out, but still leaving one result in Column E that shouldn't be there
英文:
I am trying to filter out all results on Column 5 except for one called "Pending". Often, there are no "Pending" variables on the report, so I should be left with only blanks and column headers. Every time I run the code though, it returns a single result showing a variable called "Inactive" which should be filtered out. All the other "Active" and "Inactive" results are filtered out, but it seems to think that SOMETHING has to display since it can't all be blank.
I read that sometimes you can use "=" to represent blanks in the autofilter, but it doesn't appear to work.
How do I tell VBA that I'm okay if the variable I am looking for doesn't exist on this report and it's okay to display blank results?
Current code:
Sub PepperH()
Worksheets("Sheet2").Select
Range("A9:L3000").AutoFilter Field:=5, Criteria1:="Pending", Operator:=xlOr, Criteria2:="="
'Should only show pending accounts- headers run A1-A8 so we need to start after that
End Sub
End result of that code is the report looks like this: vast majority filtered out, but still leaving one result in Column E that shouldn't be there
答案1
得分: 0
Notice that row 9 is the visible row, and the filter dropdown buttons are on row 9.
Range("A9:L3000").AutoFilter
means you are using row 9 as your header row.
Range("A8:L3000").AutoFilter
is probably what you want.
英文:
Notice that row 9 is the visible row, and the filter dropdown buttons are on row 9.
Range("A9:L3000").AutoFilter
means you are using row 9 as your header row.
Range("A8:L3000").AutoFilter
is probably what you want.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论