Solved: Issue with applying a name to a range in autofiltered table

[touyets]

Hi all,

first post on this forum but hoping you can help.

I have an excel 2007 table with rows A, B, C and D

I have a userform with a listbox for which I am trying to populate a list based on a named range. The name of the range is "Filtered".

I have a little textbox in the userform which will then autofilter the "table1" and that is working perfectly but when I have the below code and then check the list of data in "Filtered" in the Name Manager in excel, only the first visible line of the filtered table is visible and not the 19 others...

ActiveWorkbook.Names.Add Name:="Filtered", RefersToR1C1:=Range("Table1").SpecialCells(xlCellTypeVisible).Columns(1).Ce lls

I have no clue as to why it isn't picking up the 20 cells for taht specific filtering (the length is variable on the filtering done previously of course).

Any help would be very appreciated.

Simon

[p45cal]

because such a range will only be the first area of the non-contiguous range.
something like this perhaps:[VBA]Sub blah()
ListBox1.Clear
For Each cll In Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Cells
ListBox1.AddItem cll.Value
Next cll
End Sub
[/VBA]

[mikerickson]

Perhaps
[VBA]Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Name = "Filtered"[/VBA]

[touyets]

because such a range will only be the first area of the non-contiguous range.
something like this perhaps:[vba]Sub blah()
ListBox1.Clear
For Each cll In Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Cells
ListBox1.AddItem cll.Value
Next cll
End Sub
[/vba]

Worked perfectly thanks

[p45cal]

Perhaps
[vba]Range("Table1").Columns(1).SpecialCells(xlCellTypeVisible).Name = "Filtered"[/vba]

mikerickson, what code do you then use to fill the listbox with that named range?

[mikerickson]

It appeared that the issue was how to name a discontinuous range.
Using Columns(1).SpecialCells instead of SpecialCells.Columns(1)

As you pointed out, naming the range is not needed to loop through its cells.

[p45cal]

It appeared that the issue was how to name a discontinuous range.
Using Columns(1).SpecialCells instead of SpecialCells.Columns(1)

As you pointed out, naming the range is not needed to loop through its cells.

I hadn't noticed that!
I thought the issue was trying to assign a non-contiguous range to a listbox, which so far, I've not found a way of doing in a single statement (though I haven't looked very hard).