Hello all,
I need a vba macro that returns in one cell the values of multiple columns with spaces between, if criteria x, like this: A = ENGC QM02 SD02
code ENGC FCAB QM02 SD02
A X X X
B X X
C
D X X
E X X
Thank you!
Hello all,
I need a vba macro that returns in one cell the values of multiple columns with spaces between, if criteria x, like this: A = ENGC QM02 SD02
code ENGC FCAB QM02 SD02
A X X X
B X X
C
D X X
E X X
Thank you!
This is a UDF which must be pasted into a standard code module (none of the modules available in a blank workbook; you have to add it and its name would be 'Module1' by default).
Function Concat(RowNumber As Long) As String
Dim Fun As String ' Function result
Dim Rng As Range
Dim Cell As Range
With Range("Captions")
Set Rng = .Offset(RowNumber - .Row)
For Each Cell In Rng
If Len(Trim(Cell.Value)) Then
Fun = Fun & .Cells(Cell.Column - .Column + 1).Value & " "
End If
Next Cell
End With
Concat = Trim(Fun)
End Function
This UDF uses the named range "Captions". In my test I placed this range in $B$2:$E$2 to simulate the sheet you posted. You can use this kind of address instead of a named range but a named range is easier to maintain.
You call the function from your worksheet with
= Concat(ROW())
. It will evaluate the cells under the "Captions" range in the row where it is located and return the concatenated string of column captions. Note that it looks for any content in the cells, not necessarily "x". Anything which is not a blank cell will trigger inclusion of the caption in the concatenated list.
In the attached file the "Captions" range is $B$16:$DG$16 and the UDF call is in column DH. Observe that the file must be saved in XLSM format because it contains code now.
Fun = Fun & .Cells(Cell.Column - .Column+1).Value & " "