Need trim function included in the transpose function, can someone help me on this.

[tv9_rohith]

Hi

Currently Im using formula:

Dim LR As Long
LR = Range("A" & Rows.count).End(xlUp).Row
Range("G9").Value = Join(Application.Transpose(Range("A2:A" & LR)), ", ")

but I want include Trim function in it.

before transpose the data first it should trim it then it should transpose.

could someone help me on this.

Regards,
Rohith M

 

[VoG]

Yes.

Perhaps you have spaces in column A. Make sure the cells are actually blank (select them, press Delete).

 

[tv9_rohith]

I have the header in column A ... is it becoz of that I am getting error?

 

[VoG]

Try this

Private Sub CommandButton1_Click()
Dim LR As Long, s As String, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If LR = 1 Or WorksheetFunction.CountA(Range("A2:A" & LR)) = 0 Then
    MsgBox "Please enter the data", vbExclamation
    Exit Sub
End If
For i = 2 To LR
    s = s & Trim(Range("A" & i).Value) & ", "
Next i
If s = "" Then
    MsgBox "No data found", vbExclamation
Else
    Range("G9").Value = Left(s, Len(s) - 2)
End If
End Sub

 

[tv9_rohith]

Grt!!!!!!!!!!!!!!!!!!

Its working!!!!!!!!!!!!!!!!!

I have very very small request from you.

I have spreadsheet where I have 1 text box and 2 command buttons.

I wanted to use my Keyboard tab button to switch between the text box and the Command button.

how can I do that ?

Thanks a Lot.

 

[VoG]

Try starting a new thread for that question.

 

[tv9_rohith]

I have started a new Thread for that.

Thank you.

If possible could you please check that.

Thanks again...