Naming a range for a listbox

**Josh_123456** · 03-15-2011, 08:04 PM

I am trying to use the ColumnHeads feature in the listbox. The only issue I have is I have always used the .Add Item method.

If all my data is on sheet (Data), how do I name a range thru code to fill the listbox property so I can set the ColumnHeads to true.

Row 1 is a Header Row
A thru E have data
Last row may vary

Thanks for any help or feedback,

Josh

**Leith Ross** · 03-15-2011, 08:41 PM

Hello Josh,

Here is an example of sizing the range before the UserForm is displayed.

Private Sub UserForm_Initialize()

  Dim Rng As Range
  
    Set Rng = Range("A1:E1")
    
    Set LastRow = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, , False)
    If LastRow Is Nothing Then Exit Sub
    
      Set Rng = Rng.Resize(RowSize:=LastRow.Row - Rng.Row + 1)
      Set Rng = Rng.Offset(1, 0).Resize(RowSize:=Rng.Rows.Count - 1)
      
      With Me.ListBox1
        .ColumnCount = Rng.Columns.Count
        .ColumnHeads = True
        .RowSource = "=" & Rng.Parent.Name & "!" & Rng.Address
      End With

End Sub

**Josh_123456** · 03-20-2011, 10:14 AM

Hi Leith,

Thank you for the response.

I am getting an error with LastRow... Saying it needs an object ???

I tried... Dim LastRow as Long but that didn't work.

Josh

**royUK** · 03-20-2011, 11:06 AM

Declare LastRow as a Range

Private Sub UserForm_Initialize()

  Dim Rng As Range
  Dim LastRow as Range
    Set Rng = Range("A1:E1")
    
    Set LastRow = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, , False)
    If LastRow Is Nothing Then Exit Sub
    
      Set Rng = Rng.Resize(RowSize:=LastRow.Row - Rng.Row + 1)
      Set Rng = Rng.Offset(1, 0).Resize(RowSize:=Rng.Rows.Count - 1)
      
      With Me.ListBox1
        .ColumnCount = Rng.Columns.Count
        .ColumnHeads = True
        .RowSource = "=" & Rng.Parent.Name & "!" & Rng.Address
      End With

End Sub

**Josh_123456** · 03-22-2011, 12:00 AM

thanks Roy... I am able to get it to work....

The only problem is it seems to be stopping the range on row 11

I have used a message box to show what the range and it is showing A2:F10 in both of the instances below.... do you know where it is being caught up on row 11?

Here is the full code I am using to load a listbox with a range so I can use the column heads

HTML Code:

Thanks and appreciate the help...

Josh

**royUK** · 03-22-2011, 04:02 AM

Looks like you have changed the Resize part

'Original:
Set Rng = Rng.Offset(1, 0).Resize(RowSize:=Rng.Rows.Count - 1)
'Now:
 Set Rng = Rng.Resize(, 89).SpecialCells(xlCellTypeVisible)

I think you need to attach the workbook

**Josh_123456** · 03-22-2011, 08:23 PM

I kept Leith's part of the code the same accept for the A1 to E1, I changed to A1:F1....

If you look at the first part of the code, this was always separate... this basically sends the filtered data I need to the sheet Campaign Filter.

Then from Campaign Filter, I want to name the range so I can load the listbox and use the column heads.

Under the comment 'Set Range for listbox is the identical code accept for the change above of adding one column....

For some reason it sets the range to A2:F11 of campaign filter for the listbox even though there are more rows

Hope that clarifies,

Josh

**royUK** · 03-23-2011, 05:22 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!