1.I have a worksheet with range" A1:H 1" which serves as the row source for
my list box. However, I dont want column B and A displayed in the list box.
This does not happen even if I hide the colums in the worksheet manually

2. In the same worksheet, I want to filter by Column B such that items not
part of the filtered range does not show in the list box. This does not
happen also in listbox

Can Someone please help me.
thank you in advance

#1. Why do you have to include columns A:B in the rowsource? Is there a
reason? If there is, then how about setting the columnwidth property to
"0;0;......" (hiding the two columns in the listbox).

#2. You'll have to drop the rowsource property and use .additem. Loop through
the range and only add the data on the row for the visible rows.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim vRng As Range
Dim myCell As Range
Dim iCol As Long

Set wks = ThisWorkbook.Worksheets("Sheet1")

With Me.ListBox1
.ColumnCount = wks.Range("a1:H1").Cells.Count
.MultiSelect = fmMultiSelectSingle '?????
.ColumnWidths = "0;0;25;25;25;25;25;25"
End With

With wks.AutoFilter.Range
Set vRng = Nothing
On Error Resume Next
'single column, exclude header and resize to exclude header
Set vRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

With Me.ListBox1
If vRng Is Nothing Then
'nothing visible, what should happen
.Enabled = False
Else
For Each myCell In vRng.Cells
.AddItem myCell.Value
For iCol = 1 To .ColumnCount - 1
.List(.ListCount - 1, iCol) = myCell.Offset(0, iCol).Value
Next iCol
Next myCell
End If
End With

End Sub


Yomi wrote:

1.I have a worksheet with range" A1:H 1" which serves as the row source for
my list box. However, I dont want column B and A displayed in the list box.
This does not happen even if I hide the colums in the worksheet manually

2. In the same worksheet, I want to filter by Column B such that items not
part of the filtered range does not show in the list box. This does not
happen also in listbox

Can Someone please help me.
thank you in advance

--

Dave Peterson

OssieMac, this is wonderful!. It worked for the column. However, for the
second part, the hiidden rows on the filtered range still show in the List
box.

Can you or anyone help please. I dont want hidden rows(when autofilter is
used) to show in the list box

Regards

"OssieMac" wrote:

Set the ColumnWidths property of the list box. The column width of the
columns not to be seen set to zero. The column widths are entered in points
so for columns to be viewed do not use anything less than about 15-20 to
start with and then adjust as required after viewing the end result.

You can simply set the column widths as a numbers separated by commas and
Excel will then adjust with pt suffix and place semicolons between them.

Example: Enter the column widths as follows
15,0,30,30

Excel will adjust to following
15 pt;0 pt;30 pt;30 pt

--

Regards,

OssieMac


"Yomi" wrote:

1.I have a worksheet with range" A1:H 1" which serves as the row source for
my list box. However, I dont want column B and A displayed in the list box.
This does not happen even if I hide the colums in the worksheet manually

2. In the same worksheet, I want to filter by Column B such that items not
part of the filtered range does not show in the list box. This does not
happen also in listbox

Can Someone please help me.
thank you in advance