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]

Try

Dim LR As Long, s As String, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    s = s & Trim(Range("A" & i).Value) & ", "
Next i
Range("G9").Value = Left(s, Len(s) - 2)

 

[tv9_rohith]

And samll addition to that - If there is no data in the colum A then, it should display msgbox "Please enter the data ". if there is a data then it will execute the below formular.

Dim LR As Long, s As String, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
s = s & Trim(Range("A" & i).Value) & ", "
Next i
Range("G9").Value = Left(s, Len(s) - 2)

 

[VoG]

Try

Dim LR As Long, s As String, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If 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
Range("G9").Value = Left(s, Len(s) - 2)

 

[tv9_rohith]

I am getting error for line - Range("G9").Value = Left(s, Len(s) - 2).

could you please check it

 

[VoG]

I don't get an error. What is the exact error message? What values do you have in column A?

My quick test

Excel Workbook
	A	B	C	D	E	F	G
2	a
3	b
4	d
5
6
7
8
9							a, b, d
Sheet2

 

[tv9_rohith]

I have assigned this formula to Commandbutton and without entering the data in Coulmn A I thried using this formula and the code is not getting executed.

Want want is if there is no data in coulmn A and if we try to click tha command button then it should show up the message like Please enter the data.

If I enter the data in colum A then press the command button then it should execute the formula - Trim, Transpose .

I think this is clear

 

[tv9_rohith]

if the data is entered in colum A then its giving the result!!!!!!!!!

 

[VoG]

This works fine for me with the button on the same sheet as the data

Private Sub CommandButton1_Click()
Dim LR As Long, s As String, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If 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
Range("G9").Value = Left(s, Len(s) - 2)
End Sub

 

[tv9_rohith]

R u getting the message box ? when there is no data in Column A ?