获取单元格中的值列表
问题描述:
我有一张Excel工作表,其中创建了一个由多个值组成的列表。另外我创建了一个宏,它显示了一个用户窗体,其中这些值是硬编码的。获取单元格中的值列表
现在我希望窗体中的这些值自动/编程/动态添加到我的用户窗体列表中,以便将来如果我想减少列表中的值,那么我不必更改宏再次。
我一直在寻找答案,但我一直没有找到我要找的东西。
我记录了这个宏,但我不知道如何从中检索值:
Sub Macro7()
'
' Macro7 Macro
'
'
Range("E1").Select
ActiveSheet.Range("$A$1:$AE$175").AutoFilter Field:=5
End Sub
答
您指定将打开自动筛选你的活动工作表宏。这将提供列标题,允许用户过滤到感兴趣的内容。 假设这种工作表中筛选的是你想要的,你可以使用的东西,像什么:
Dim r As Range
'Note: set r to something useful, such as worksheet.Cells
Dim vis As Range
Set vis = r.SpecialCells(xlCellTypeVisible)
'now vis holds a special "Range" object referring to the visible cells.
'since (auto) filtering hides some cells, this vis range will help show only the cells that remain visible.
'the output of SpecialCells, you should assume holds a complex Range,
'which is composed of multiple Areas that are wrapped in one single Range object
'the separate areas help you distinguish the visible cells from the hidden cells
'fyi, various safety checks you can do: vis Is Range, vis Is Nothing
Dim a as Areas
Set a = r.Areas
Dim cr as Range
For Each cr in a
'cr refers to a single (i.e. normal and contiguous) area range
'where you can use cr.Row, cr.Column, cr.Rows.Count, cr.Columns.Count
Next
所以当你做过滤,可以使用SpecialCells(xlCellTypeVisible)揭示非隐藏的细胞,被表示为具有包围代表连续范围的区域的范围。
答
与具有一个名为lbxReport列表框,这样使用的代码与来自E列
Sub ShowUf()
Dim ufReports As UReports
Dim rCell As Range
Dim colUnique As Collection
Dim i As Long
Set ufReports = New UReports
Set colUnique = New Collection
'loop through the cells in column E
For Each rCell In Sheet1.Range("E2", Sheet1.Cells(Sheet1.Rows.Count, 5).End(xlUp)).Cells
'Collections can't have duplicate keys, so we try to add all the values. If there
'are duplicates, the 'On Error' ignores them and we're left with a collection of
'only unique values from column E
On Error Resume Next
colUnique.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
Next rCell
'loop through the collection and add them to the listbox
For i = 1 To colUnique.Count
ufReports.lbxReport.AddItem colUnique.Item(i)
Next i
'Show the form
ufReports.Show
End Sub
值填写列表框一个窗体命名UReports这是列表中用户窗体数组?或在一个组合框?或一个列表框?或者是其他东西? – 2012-07-19 16:28:14
我想要在Excel单元格中获取的值是以列表的形式。并启用了过滤器。我想在用户表单列表框中添加这些值。 – IConfused 2012-07-19 16:32:32
在我给你的代码之前,也许你想通过循环工作表中的列表来尝试自己,然后使用'ListBox1.Add'来添加新项目? – 2012-07-19 16:39:56