I have a userform listbox. This list box is currently populated by range
A4,F173 Column F is the only one with an entry in every used row. All rows
from A4 to F(last row) will have data in them, but not in all columns except
for column F. How can I define this range to capture the range from
A4,F(last used)?

This is what I have been trying to get to work.

Private Sub UserForm_Initialize()
Me.ListBox1.Clear

Dim ListBoxRange As Range
Dim LastRow As Long

LastRow = Cells(Rows.Count, "F").End(xlUp).Row

Set ListBoxRange = Range(A4, Cells(LastRow, "F"))

Me.ListBox1.List = Sheet1.Range("ListBoxRange").Value
End Sub

"AUCP03" wrote:

> I have a userform listbox. This list box is currently populated by range
> A4,F173 Column F is the only one with an entry in every used row. All rows
> from A4 to F(last row) will have data in them, but not in all columns except
> for column F. How can I define this range to capture the range from
> A4,F(last used)?

Private Sub UserForm_Initialize()

Dim ListBoxRange As Range
Dim LastRow As Long

with worksheets("somesheetnamehere")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set ListBoxRange = .Range("A4:F" & LastRow)
end with

with me.listbox1
.clear
.columncount = listboxrange.columns.count
.list = listboxrange.value
end with

End Sub

AUCP03 wrote:
>
> This is what I have been trying to get to work.
>
> Private Sub UserForm_Initialize()
> Me.ListBox1.Clear
>
> Dim ListBoxRange As Range
> Dim LastRow As Long
>
> LastRow = Cells(Rows.Count, "F").End(xlUp).Row
>
> Set ListBoxRange = Range(A4, Cells(LastRow, "F"))
>
> Me.ListBox1.List = Sheet1.Range("ListBoxRange").Value
> End Sub
>
> "AUCP03" wrote:
>
> > I have a userform listbox. This list box is currently populated by range
> > A4,F173 Column F is the only one with an entry in every used row. All rows
> > from A4 to F(last row) will have data in them, but not in all columns except
> > for column F. How can I define this range to capture the range from
> > A4,F(last used)?

--

Dave Peterson

Thank you for the help Mr. Peterson! It works great.

"Dave Peterson" wrote:

> Private Sub UserForm_Initialize()
>
> Dim ListBoxRange As Range
> Dim LastRow As Long
>
> with worksheets("somesheetnamehere")
> LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
> Set ListBoxRange = .Range("A4:F" & LastRow)
> end with
>
> with me.listbox1
> .clear
> .columncount = listboxrange.columns.count
> .list = listboxrange.value
> end with
>
> End Sub
>
> AUCP03 wrote:
> >
> > This is what I have been trying to get to work.
> >
> > Private Sub UserForm_Initialize()
> > Me.ListBox1.Clear
> >
> > Dim ListBoxRange As Range
> > Dim LastRow As Long
> >
> > LastRow = Cells(Rows.Count, "F").End(xlUp).Row
> >
> > Set ListBoxRange = Range(A4, Cells(LastRow, "F"))
> >
> > Me.ListBox1.List = Sheet1.Range("ListBoxRange").Value
> > End Sub
> >
> > "AUCP03" wrote:
> >
> > > I have a userform listbox. This list box is currently populated by range
> > > A4,F173 Column F is the only one with an entry in every used row. All rows
> > > from A4 to F(last row) will have data in them, but not in all columns except
> > > for column F. How can I define this range to capture the range from
> > > A4,F(last used)?
>
> --
>
> Dave Peterson
>