blocks|key|2310300|text|基本上，自动筛选所做的是隐藏与所选内容不匹配的整行。在本例中，如果在B列中，我将假定您正在查找的信息|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2310301|'getting+the+value+I+am+looking+for
++cbVal+=+comboBox1.Text

+'getting+the+last+row+filled
+lr+=+activesheet.cells(rows.count,2).end(xlup).row


'asuming+that+B1+is+the+title+of+the+table,+so+I'm+starting+since+B2+to+last+row

for+i+=+2+to+lr
if+not+activesheet.range("B"&i)+==+cbVal+then
++activesheet.range("B"&i).entireRow.Hidden+=+true
end+if
next+i|code-block|syntax|javascript|2310302|最后，您设置了一个用于隐藏行的sub|2310303|for+i+=+2+to+lr
activesheet.range("B"&i).hidden+=+false
next+i|2310304|希望它已经解决了你的问题，我还没有测试你，但是我的想法是你得到了逻辑|2310305|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Basically, what an autofilter does is that it hides the entirerows that dont match the selection right. In this example, I'll asume the info you are looking for if in column B

<pre><code>'getting the value I am looking for
 cbVal = comboBox1.Text

 'getting the last row filled
 lr = activesheet.cells(rows.count,2).end(xlup).row


'asuming that B1 is the title of the table, so I'm starting since B2 to last row

for i = 2 to lr
if not activesheet.range("B"&amp;i) == cbVal then
 activesheet.range("B"&amp;i).entireRow.Hidden = true
end if
next i
</code></pre>

Finally, yo set anothe sub for unhiding the rows

<pre><code>for i = 2 to lr
activesheet.range("B"&amp;i).hidden = false
next i
</code></pre>

Hope it has solved your problem, I haven't tested yer but the ideas is that you get the logics

blocks|key|2522062|text|所以我不确定我是否100%25的做到了你想要做的，但是这里有一个方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2522063|以下方法适用于ActiveX组合框。|2522064|为了简单起见，我们假设您有以下设置：|2522065|​|2522066|📷|atomic|offset|length|2522067|2522068|筛选器值在A1:A8中，您希望按日期名称筛选列C。|style|CODE|2522069|首先，您需要一个用于初始化combobox的sub。这可以在你的模块中找到。如何调用此sub取决于您。如果这个列表需要定期更新，你可以有一个专门的按钮。|2522070|Sub+initializeComboBox()+'The+name+of+the+Sub+is+self+explanatory

Dim+sht+As+Worksheet
Set+sht+=+ThisWorkbook.Worksheets("Name+of+your+Worksheet")+'The+worksheet+where+the+combobox+is+located
With+sht.OLEObjects("ComboBox1").Object+'Referring+to+the+combobox+from+outside+the+worksheet+where+it's+located+can+be+a+bit+tricky
++++.Clear+'Clear+the+combobox's+list
++++.List+=+sht.Range("A1:A8").Value+'assign+the+values+contained+in+range+A1:A8+to+the+combobox's+list
End+With

End+Sub|code-block|syntax|javascript|2522071|然后，您需要一个事件，该事件将在用户选择组合框时捕获组合框的值的更改。它需要位于combobox所属的工作表中。|2522072|Private+Sub+ComboBox1_Change()+'This+event+fires+up+whenever+the+user+selects+an+item+from+the+dropdown+menu
Dim+x+As+String
x+=+ComboBox1.Value+'referring+to+the+combobox+from+inside+the+worksheet+where+it+belongs+is+easier
Module1.testFilter+(x)
End+Sub|2522073|然后，您可以拥有一个位于模块中并由该事件调用的sub，也可以使用事件本身做任何您想做的事情。我在这里应用了第一种方法，但它实际上是您的选择。|2522074|Sub+testFilter(filterValue+As+String)+'A+sub+that+is+located+in+the+module+and+applies+the+filter.+This+is+called+whenever+the+Value+of+the+combobox+is+changed

Dim+sht+As+Worksheet
Dim+rngToBeFiltered+As+Range
Set+sht+=+ThisWorkbook.Worksheets("Name+of+your+Worksheet")+'The+worksheet+where+the+range+that+needs+to+be+filtered+is+located
Set+rngToBeFiltered+=+sht.Columns("C")
If+filterValue+=+"No+Filter"+Then
++++rngToBeFiltered.AutoFilter+field:=1+'If+No+Filter+is+selected+then+all+data+is+displayed
Else
++++rngToBeFiltered.AutoFilter+field:=1,+Criteria1:=filterValue+'else+the+filter+is+applied
End+If

End+Sub|2522075|结果是这样的：|2522076|2522077|2522078|2522079|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/6f093982a026a8f1a1d899e080a1a9f1.jpeg|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/17c58905e555530d63c5c727bb1bdeac.jpeg^0|0|0|0|0|0|1|0|0|0|5|5|0|17|3|0|0|0|0|N|3|0|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1O|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1P|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1Q|8|@]|9|@$K|1R|L|1S|1|1T]]|A|$]]|$1|M|3|G|5|6|7|1U|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1V|8|@$K|1W|L|1X|P|Q]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1Y|8|@$K|1Z|L|20|P|Q]]|9|@]|A|$]]|$1|T|3|U|5|V|7|21|8|@]|9|@]|A|$W|X]]|$1|Y|3|Z|5|6|7|22|8|@]|9|@]|A|$]]|$1|10|3|11|5|V|7|23|8|@]|9|@]|A|$W|X]]|$1|12|3|13|5|6|7|24|8|@$K|25|L|26|P|Q]]|9|@]|A|$]]|$1|14|3|15|5|V|7|27|8|@]|9|@]|A|$W|X]]|$1|16|3|17|5|6|7|28|8|@]|9|@]|A|$]]|$1|18|3|G|5|6|7|29|8|@]|9|@]|A|$]]|$1|19|3|I|5|J|7|2A|8|@]|9|@$K|2B|L|2C|1|2D]]|A|$]]|$1|1A|3|G|5|6|7|2E|8|@]|9|@]|A|$]]|$1|1B|3|-4|5|6|7|2F|8|@]|9|@]|A|$]]]|1C|$1D|$5|1E|1F|1G|A|$1H|1I|1J|-4]]|1K|$5|1E|1F|1G|A|$1H|1L|1J|-4]]]]

So I am not sure I get 100% what you're trying to do, but here's one way to do it:

The following approach is meant to work with an ActiveX combobox.

For the sake of simplicity, let's assume you have the following setup:

<a href="https://i.stack.imgur.com/tdGsB.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tdGsB.jpg" alt="enter image description here"></a>

The filter values are in <code>A1:A8</code> and you want to filter column C, by the name of day.

Firstly, you need a sub that initializes the combobox. This can be located in your module. How this <code>sub</code> is called is up to you. If the list needs to be updated regularly you could have a dedicated button for this. 

<pre><code>Sub initializeComboBox() 'The name of the Sub is self explanatory

Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Name of your Worksheet") 'The worksheet where the combobox is located
With sht.OLEObjects("ComboBox1").Object 'Referring to the combobox from outside the worksheet where it's located can be a bit tricky
 .Clear 'Clear the combobox's list
 .List = sht.Range("A1:A8").Value 'assign the values contained in range A1:A8 to the combobox's list
End With

End Sub
</code></pre>

Then you need an event that will capture the change of the combobox's value whenever the user selects one. This needs to be located in the worksheet to which the combobox belongs.

<pre><code>Private Sub ComboBox1_Change() 'This event fires up whenever the user selects an item from the dropdown menu
Dim x As String
x = ComboBox1.Value 'referring to the combobox from inside the worksheet where it belongs is easier
Module1.testFilter (x)
End Sub
</code></pre>

Then you could either have a <code>sub</code> that is located in your module and is being called by this event, or you could just do anything you want to, using the event itself. I applied the first approach here but it's really your choice.

<pre><code>Sub testFilter(filterValue As String) 'A sub that is located in the module and applies the filter. This is called whenever the Value of the combobox is changed

Dim sht As Worksheet
Dim rngToBeFiltered As Range
Set sht = ThisWorkbook.Worksheets("Name of your Worksheet") 'The worksheet where the range that needs to be filtered is located
Set rngToBeFiltered = sht.Columns("C")
If filterValue = "No Filter" Then
 rngToBeFiltered.AutoFilter field:=1 'If No Filter is selected then all data is displayed
Else
 rngToBeFiltered.AutoFilter field:=1, Criteria1:=filterValue 'else the filter is applied
End If

End Sub
</code></pre>

The result would like that:

<a href="https://i.stack.imgur.com/8SLoB.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8SLoB.jpg" alt="enter image description here"></a>

blocks|key|2310517|text|我最终将操作拆分为两个模块:+M_00_Filter()和M_00_Generate()。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2310518|第一种方法根据从用户窗体组合框中选择的内容来筛选数据，该组合框静态地分配了同一工作簿中另一个工作表的范围。|2310519|Sub+M_00_Filter()

Dim+wB+As+Workbook
Dim+myindex+As+Long
Dim+filterValue+As+String
Dim+wS+As+Worksheet
Dim+rng2BF+As+Range

Set+wB+=+ThisWorkbook
Set+wS+=+wB.Sheets("Master+Schedule")

'~~>+Currently+selected+item+index+at+runtime
myindex+=+wS.Shapes("Drop+Down+1").ControlFormat.Value

'~~>+Currently+selected+item+value+at+runtime
filterValue+=+wS.Shapes("Drop+Down+1").ControlFormat.List(myindex)

lRow+=+wS.Cells(wS.Rows.Count,+4).End(xlUp).Row
lCol+=+wS.Cells(5,+wS.Columns.Count).End(xlToLeft).Column

'Set+rngToBeFiltered
Set+rng2BF+=+wS.Range(Cells(5,+1),+Cells(lRow,+lCol))

If+filterValue+=+"Select+a+Phase"+Then

++++rng2BF.AutoFilter+field:=4+'If+No+Filter+is+selected+then+all+data+is+displayed

Else

++++rng2BF.AutoFilter+field:=4,+Criteria1:=filterValue+'else+the+filter+is+applied

End+If

End+Sub|code-block|syntax|javascript|2310520|第二个模块复制可见数据并将其复制到另一个工作簿。再次感谢您的帮助。|2310521|感谢大家抽出时间来。干杯。|2310522|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I ended up splitting the operation into two modules, M_00_Filter() and M_00_Generate().

The first filters the data based on the selection from a user form combobox statically assigned a range from another worksheet in the same workbook.

<pre><code>Sub M_00_Filter()

Dim wB As Workbook
Dim myindex As Long
Dim filterValue As String
Dim wS As Worksheet
Dim rng2BF As Range

Set wB = ThisWorkbook
Set wS = wB.Sheets("Master Schedule")

'~~&gt; Currently selected item index at runtime
myindex = wS.Shapes("Drop Down 1").ControlFormat.Value

'~~&gt; Currently selected item value at runtime
filterValue = wS.Shapes("Drop Down 1").ControlFormat.List(myindex)

lRow = wS.Cells(wS.Rows.Count, 4).End(xlUp).Row
lCol = wS.Cells(5, wS.Columns.Count).End(xlToLeft).Column

'Set rngToBeFiltered
Set rng2BF = wS.Range(Cells(5, 1), Cells(lRow, lCol))

If filterValue = "Select a Phase" Then

 rng2BF.AutoFilter field:=4 'If No Filter is selected then all data is displayed

Else

 rng2BF.AutoFilter field:=4, Criteria1:=filterValue 'else the filter is applied

End If

End Sub
</code></pre>

The second module copies the visible data and copies it out to another workbook.
Thanks again for your help.

Appreciate you all taking the time. 
Cheers.

Copying filtered data to another worksheet where user inputs filter criteria via ComboBox (userform or ActiveX) 

Ultimately, I want to copy out select data from a master data set on one worksheet to another worksheet. To isolate the relevant data for copying across I plan to use filters. I want to include a drop down list object within the worksheet that will allow the user to select the filter criteria 

This is essentially just replicating the Excel data filter function but without being positionally restricted to the column being filtered.

I am assuming the Userform or ActiveX ComboBox is the way to achieve this? This where I'm failing. I can't figure out how to call the value selected from the ComboBox by the user to inform the AutoFilter procedure.

<pre><code>Sub M_00()

 Dim wB As Workbook
 Dim wS1, wS2 As Worksheet
 'Dim x As ???
 Dim rng3 As Range
 Dim lrowS3 As Long

 Set wB = This.Workbook
 Set wS1 = wB.Sheets(1)
 Set wS3 = wB.Sheets(3)

 'define range for Combo Box drop down list from data on Sheet 3
 lrowS3 = wS3.Cells(Rows.Count, 1).End(xlUp).Row

 Set rng3 = wS3.Range(Cells(2, 2), Cells(lrowS3, 2))

 'Don't know how to
 ' a) assign my rng3 values to the ComboBox drop down list
 ' b) call the selection from the ComboBox to inform my AutoFilter

 wS1.ComboBox1.List = rng3.Value

 x = wS1.ComboBox1.Selection

 ' turn off any autofilters that may already be active in Sheet 1
 wS1.AutoFilterMode = False

 ' filter column 4 based on combo box selection
 wS1.UsedRange.AutoFilter Field:=4, Criteria1:=x

 'Once filtered I plan you assign the visible data to an array and copy 
 'it to the required sheet.
End Sub
</code></pre>

Utilising controls (ComboBox) within Worksheet to control Auto filter

将筛选的数据复制到用户通过ComboBox (用户表单或ActiveX)输入筛选条件的另一个工作表最终，我希望将选择的数据从一个工作表上的主数据集中复制到另一个工作表。为了隔离要复制的相关数据，我计划使用过滤器。我想在工作表中包含一个下拉列表对象，允许用户选择筛选条件这本质上只是复制Excel数据筛选函数，而不是在位置...

问利用工作表中的控件(ComboBox)控制自动筛选
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问利用工作表中的控件(ComboBox)控制自动筛选EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问利用工作表中的控件(ComboBox)控制自动筛选
EN