Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge = 1 Then
' B2 is the cell with drop down list
If Target.Address = "$B$2" Then
Call showIf(Target.Value2)
End If
End If
End Sub
Sub showIf(criteria As String)
Dim colA As Range, found As Range, cnt As Long, frow As Long
Application.ScreenUpdating = False
' maybe here you want to find the last non empty row
' but as i know excel reduces the actions in the usedRange
Set colA = Me.Range("A:A")
criteria = UCase(criteria)
Err.Clear
On Error GoTo Lerr
If criteria <> "" And criteria <> "ALL" Then
' find the first row of criteria
frow = WorksheetFunction.Match(criteria, colA, 0)
' count the rows witch meet the criteria
cnt = WorksheetFunction.CountIf(colA, criteria)
' use union to add some rows on top, to be always visible
Set found = Union(Range("A1:A2"), colA(frow).Resize(cnt, 1))
' hide all rows
colA.EntireRow.Hidden = True
' show only rows meet the criteria Plus some in top
found.EntireRow.Hidden = False
' select the cell with drop down menu
Range("B2").Select
' scroll on top to see the unhidden rows
ActiveWindow.ScrollRow = 1
Else
colA.EntireRow.Hidden = False
ActiveWindow.ScrollRow = 1
End If
Exit Sub
Lerr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & Err.Source
On Error GoTo 0
' if an error occurs show all rows
colA.EntireRow.Hidden = False
End Sub
2条答案
按热度按时间svmlkihl1#
如果您有名为
data
的数据手册,则包含以下内容:然后,在不同的工作表上存储用于下拉选择的唯一事务类型的选择,包括用于选择所有值的选项。
例如,我们可以使用单元格
H2
来计算以下公式:=VSTACK(UNIQUE(FILTER(data!A:A,data!A:A<>"")),"all")
在这种情况下,产生这种溢出:
在我们存储在
B2
中的下拉数据验证中,我们可以将此溢出范围称为H2#
:在
A6
(到F6)中,头被存储:{"TYPE","TRANSACTION ID","DATE","PROD ID","STORE ID","TRANS_VALUE"}
然后在
A7
中,我们使用以下公式:此公式过滤与下拉列表中的选择相等的行。如果选择了
all
,它将过滤数据表中A列中包含任何文本的任何行。可以选择隐藏H列。
=LET(a,FILTER(data!A:F,IF(B2="all",ISNUMBER(data!C:C)),data!A:A=B2)),IFERROR(--a,a))
7jmck4yq2#
复制工作表模块中的代码: