Excel在工作表中过滤并在“用户窗体"列表框中填充过滤范围 [英] Excel Filter in sheet and populate filtered range in Userform listbox

问题描述

我是StackOverflow和Excel/VBA的新手.首先，我做了一些Google研究来帮助我解决问题，但是目前还没有很好的解释，希望大家能提供帮助.

I'm new to StackOverflow AND Excel/VBA. First i did some Google research to help me with my problem but there was not a good explanation available, i hope you guys can help.

我的问题:

我有一个工作表范围是A3:V100，它填充了我的列表框.现在，我在工作表中将记录的宏与自动过滤器一起使用，并将这些宏添加到用户窗体的按钮中，但是由于结果可能是第5行，第35行，第40行，因此我的范围不再正确.我怎样才能只为过滤后的行而不是整个数据行设置一个列表框范围.

I have a worksheet range from A3:V100 that populates my listbox. Now, i use recorded macros with autofilters in my worksheet and i'm adding those macros to buttons in my userform but because the results can be row 5, row 35, row 40, my range isn't correct anymore. How can i set a listbox range only for the filtered rows and not for the whole data rows.

我当前的范围代码:

    Sub populateListbox1()

With ListBox1

    .ColumnHeads = True
    .ColumnCount = 30
    .ColumnWidths = "30;30;50;50;60;50;50;50;50;50;50;50;50;0;0;0;0;0;0;0;50;50;0;0;0;0;0;0;0;0"
    .RowSource = Sheet1.Range("A6", Range("AD" & Rows.Count).End(xlUp)).Address
End With

End Sub

我问:

当我应用自动过滤器时，谁能给我正确的rowSource范围代码.还是有人有其他想法来过滤我的列表框?

Can anyone give me a correct code for my rowSource range when i applied the autofilters. Or does someone have other ideas to filter my listbox?

提前谢谢！

推荐答案

如果找到了一种解决方法.

If found a way to do this.

我使用转到特殊内容"仅可见单元格"，我选择了结果，然后将这些值复制到另一张纸上. 我将这些步骤记录为一个宏.

I use "Go to special" "Visible cells only" i select my results, copy those value in another sheet. I recorded those steps as a macro.

毕竟，我像其他列表框一样填充列表框.

And after all that i populate my listbox like any other listbox.

这篇关于Excel在工作表中过滤并在“用户窗体"列表框中填充过滤范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！