Hi friends,
I want to concatenate the 3 different columns in column 'g, ac and ad' respectively. I'm not getting the correct output in column 'g' and the concatenate output of column 'ac and ad' respectively. The output of concatenate 'ac and ad' displays in a single column 'g' and the concatenate output of column 'g' is not displaying in column 'g'.
Please see the following code and suggest me the correction to get correct output.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count = 1 Then
Select Case .Column
Case 28 'Column AB
On Error Resume Next
Range("b" & .Row + 1).Select
On Error GoTo 0
Case 6 'Column F
On Error Resume Next
Target.Offset(, 2).Select 'This will select the next column to the right from the changed cell
On Error GoTo 0
Case 13 'Column M
On Error Resume Next
Target.Offset(, 2).Select 'This will select the next column to the right from the changed cell
On Error GoTo 0
End Select
End If
End With
'CONCATENATE column g
If Target.Row < 7 Then Exit Sub
Dim vTemp As Variant
Dim var As Variant
Dim lngArr As Long
var = Array("C", "d", "e", "f")
Application.EnableEvents = False
For lngArr = LBound(var) To UBound(var)
vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, 7).Value = vTemp
Application.EnableEvents = True
'CONCATENATE column ac
If Target.Row < 7 Then Exit Sub
var = Array("z", "aa", "ab")
Application.EnableEvents = False
For lngArr = LBound(var) To UBound(var)
vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, 7).Value = vTemp
Application.EnableEvents = True
'CONCATENATE column ad
If Target.Row < 7 Then Exit Sub
var = Array("aa", "ab")
Application.EnableEvents = False
For lngArr = LBound(var) To UBound(var)
vTemp = vTemp & Cells(Target.Row, var(lngArr)).Value & " "
Next lngArr
vTemp = Replace(Trim(vTemp), "-", " ")
Cells(Target.Row, 7).Value = vTemp
Application.EnableEvents = True
End Sub
Thanking you in anticipation.
Regards,
Mukesh
Bookmarks