只允许一个变量和空白。

huangapple go评论63阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月22日 22:35:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76307280.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定