vba 动态填充列表框 - 排除空单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2691817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically Populate Listbox - Exclude Empty cells
提问by Daniel
I am creating a form in excel (not a userform) and I am populating the listbox using cells. However, these cells are sometimes A1:10 and sometimes they are A1:A4. Is there a way to dynamically change what is shown in the listbox?
我正在 excel 中创建一个表单（不是用户表单），我正在使用单元格填充列表框。但是，这些单元格有时是 A1:10，有时是 A1:A4。有没有办法动态更改列表框中显示的内容？
Right now, when I use A1:10 and there are only 4 cells populated, I get the list of 4 populated cells followed by 6 blank entries. I'd like to get rid of the 6 blanks when there are only 4.
现在，当我使用 A1:10 并且只填充了 4 个单元格时，我得到了 4 个填充单元格的列表，然后是 6 个空白条目。当只有 4 个时，我想去掉 6 个空格。
回答by Austin A
Unfortunately, this is more of a workaround than a solution. However, it may be able to do what you need it to do. I've been hitting the same wall you are with trying to make ranges dynamic.
不幸的是，这与其说是解决方案，不如说是一种变通方法。但是，它可能能够执行您需要它执行的操作。我一直在努力使范围动态化。
Without seeing some code to know exactly what you're doing, try something like this.
没有看到一些代码来确切地知道你在做什么，试试这样的事情。
Private Sub ListBox1()
x = 1
'Add items to listbox until you reach an empty cell.
Do while Cells(x,1) <> ""
ListBox1.AddItem Cells(x,1)
Loop
I'm not very familiar with listboxes outside of userforms but this should do approximately what you want to do.
我对用户表单之外的列表框不是很熟悉，但这应该可以大致完成您想要做的事情。
Edit your original post with your code so we can try and get a better understanding of what you've tried and what you're trying to do.
使用您的代码编辑您的原始帖子，以便我们可以尝试更好地了解您尝试过的内容以及您正在尝试做的事情。
回答by Moehling
You can create a named range using a dynamic formula. Either of the below formulas will work.
您可以使用动态公式创建命名范围。以下任一公式都有效。
=A1:INDEX($A:$A,MATCH("",$A:$A,0)-1)
=OFFSET($J,0,0,MATCH("",$J:$J,0)-1)
To create the named range, click ctrl+F3 then click new, insert one of the two options above into the "refers to:" section, then label the new range whatever you would like. in the "row source" section of the listbox simply type in the name you selected for your new named range.
要创建命名范围，请单击 ctrl+F3，然后单击新建，将上述两个选项之一插入“引用：”部分，然后根据需要标记新范围。在列表框的“行源”部分，只需键入您为新命名范围选择的名称。
