TextBox9.Text = Range("J:J") --- New line for each cell with a value?

[Shaka215]

Hello Fellow Programmers!

I am in need of some assistance with getting a text box to accept a
range of cells and put the contents of each value inside the textbox on
a seprate line...I have been messing around with some of the code and
managed to get it to work correctly however I know there is a better
code to use instead of the following...

TextBox9.Value = Range("J6") & vbLf & Range("J7") & vbLf & Range("J8")
& vbLf & Range("J9") & vbLf & Range("J10") & vbLf & Range("J11") & vbLf
& Range("J12")

My code in my dreams works like this...

TextBox9.Value = Sheet1.Range("J:J") & vbLf

vbLf would only apply to nonblank values...example below.

J1 = Value
NEW LINE (vbLf)
J2 = Value
NEW LINE (vbLf)
J3 = Empty
No Line


Thanks! Hope to get some feedback...

-Todd

 

[Chip Pearson]

Try something like

Dim Rng As Range
Dim S As String
For Each Rng In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells
S = S & Rng.Text & vbCrLf
Next Rng
S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf
Me.TextBox1.Text = S

Make sure the MultiLine property of the TextBox is True.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Chip Pearson]

A slightly better version, that skips blanks cells, is

Dim Rng As Range
Dim S As String
For Each Rng In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns(10)).Cells
If Trim(Rng.Text) <> vbNullString Then
S = S & Rng.Text & vbCrLf
End If
Next Rng
S = Left(S, Len(S) - 1) 'get rid of the trailing vbCrLf
Me.TextBox1.Text = S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Shaka215]

Thanks Chip! Worked great and what was even better was your no hassle
of inputting in as a piece of code!

-Todd