PDA

View Full Version : Populate Listbox with autofiltered sheet



ProteanBeing
12-18-2007, 09:25 AM
Is there an option to only insert the filtered rows into a listbox?

Bob Phillips
12-18-2007, 09:36 AM
Dim LastRow As Long
Dim rng As Range
Dim cell As Range
Dim ary
Dim i As Long

LastRow = Cells(Rows.Count, "K").End(xlUp).Row
Set rng = Range("L1").Resize(LastRow).SpecialCells(xlCellTypeVisible)
ReDim ary(1 To rng.Cells.Count)
For Each cell In rng
i = i + 1
ary(i) = cell.Value
Next cell
UserForm1.ListBox1.List = ary

ProteanBeing
12-18-2007, 09:37 AM
For further clarification, I am creating a form that on the top allows you to sort from the master log and post into the listbox on the bottom of the form.

ProteanBeing
12-18-2007, 10:11 AM
I am having a hard time following your example. Please use the below parameters:

master log is column A through W with a header row
column W has an equation in it (goes past the last row)

Bob Phillips
12-18-2007, 10:17 AM
What is the filtered colun and which is the column loaded.

ProteanBeing
12-18-2007, 10:23 AM
Filter is B
Loaded column is all of them

Bob Phillips
12-18-2007, 10:44 AM
All A through W? How can you see them all?

ProteanBeing
12-18-2007, 11:08 AM
Vertical Scroll Bar
All Items are needed for this application

ProteanBeing
12-18-2007, 01:49 PM
I was finally able to use what you put above. However using this method I lost my column headers. Any idea how I can get them back?

Bob Phillips
12-18-2007, 03:23 PM
You can't. You only get column headers if you bind the listbox to a range, which you can't do with a discontiguus range.

figment
12-18-2007, 03:34 PM
if you can reorder the information, then you could move the info that you dont want to the bottem of the list, only show the top part of the list, this way you get to keep you headers.