Define range by last cell in column?
 

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)?

Define range by last cell in column?
 

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)?

Define range by last cell in column?
 

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

Define range by last cell in column?
 

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