I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul

to get the last row of data

Lastrow = Range("J" & Rows.count).end(xlup).row

Rows.count = 65536.
end(xlup) moves up from 65536 until first cell with data is found

Thenset the range to the code below

Lastrow = Range("J" & Rows.count).end(xlup).row
"J1:J" & LastRow

"Paul Tikken" wrote:

I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul

You can set the last row of the range to a variable. For column J.

lastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

This will find the last cell that contains data in column J. Then when
you specifiy Range("J2:J" & lastRow) it automatically finds the entire
range in column J. Just be careful to put the variable assignmen at a
place in your code where it will incorporate any changes in the column
that are made as the code executes. This would usually occur if you
have loops that add or delete rows and you would want your variable
to be assigned inside the loop so that it would reset on each iteration
of the loop. Based on how you described your set up, you might have
to assign the variable in the event code of each control.

"Paul Tikken" wrote:

I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul

Try something like this to show the userform

With UserForm1
.ListBox1.RowSource = "sheet1!j1.j" &
Application.WorksheetFunction.CountA(Worksheets("S heet1").Range("j1:j4092"))
.Show
End With


"Paul Tikken" wrote:

I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul

Here is a copy of some code I used in one of my procedures.

Private Sub UserForm_Initialize()
maxRw = Cells(Rows.Count, 8).End(xlUp).Row
x = Cells(maxRw, 8).Address
UserForm2.Caption = "SELECT STREET NAME"
With UserForm2.StrNam
.RowSource = "$H$1:" & x
.BoundColumn = 1
.ColumnCount = 1
End With
End Sub

StrNam is the name of a ListBox. The variable MaxRw finds the last row in
the range and I then set the cell address to the variable x, and use that
variable to set the row source or the list box. Note that this is done in
the initialize event of the UserForm.

This will probably be more useful than my previous posting.


"Paul Tikken" wrote:

I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul

Thanks,

This will work for me!!

Cheers,

Paul

"JLGWhiz" wrote:

You can set the last row of the range to a variable. For column J.

lastRow = ActiveSheet.Cells(Rows.Count, 10).End(xlUp).Row

This will find the last cell that contains data in column J. Then when
you specifiy Range("J2:J" & lastRow) it automatically finds the entire
range in column J. Just be careful to put the variable assignmen at a
place in your code where it will incorporate any changes in the column
that are made as the code executes. This would usually occur if you
have loops that add or delete rows and you would want your variable
to be assigned inside the loop so that it would reset on each iteration
of the loop. Based on how you described your set up, you might have
to assign the variable in the event code of each control.

"Paul Tikken" wrote:

I'm using different columns as a source for my listboxes of my userform. I've
set the range for each independent column. But these ranges may vary, because
of inputs into the userform. How can I make the listboxes adjust to these
flexible ranges?

So let's day that for column J, I've set the range from J1:J4092, but this
entire range is not filled so I only want to display the used range in column
J.

Is this possible?

Paul