Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Values from multiple columns in one cell

0

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!

Answer
Discuss

Answers

0
Selected Answer

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.

Discuss

Discussion

@Don Just to let you know, I have two messages about this threead  in my alerts. One is to say that the answer was accepted, the other that there is a discussion reply. In the thread I find neither of these and my credits unchanged. This is the second similar case today but in the other there was no missing discussion answer that I could identify.
Variatus (rep: 4889) Aug 24, '17 at 6:37 am
I put this function into a new Module in VBA and i defined the range, but i cannot make it work. I attached the file for which I want to obtain the values in the last column of each row, if you can take a look.
Thanks a lot!
sebas14 (rep: 16) Aug 24, '17 at 6:39 am
Variatus this happens when the user selects an answer and then deselects it and also when they delete their comment or discussion post. The notifications are already sent out before the users undoes their action. The email notifications also work this way.
don (rep: 1989) Aug 24, '17 at 6:47 am
Thank you @Don.
Variatus (rep: 4889) Aug 24, '17 at 6:55 am
Might be because you didn't save the file as macro enabled? I implemented the solution in the copy of your file which I attached to my answer.
Variatus (rep: 4889) Aug 24, '17 at 6:57 am
@Variatus, you right, it works now. The only remarque is that the function returns the value of the anterior column and not that where the x is placed.
What shoud be changed? 
sebas14 (rep: 16) Aug 24, '17 at 7:39 am

with Column+1 instead of Column it works:
Fun = Fun & .Cells(Cell.Column - .Column+1).Value & " "

sebas14 (rep: 16) Aug 24, '17 at 8:22 am
Sorry about that. But great that you managed to correct the code. I incorporated the change in my published answer above (but not in the attached workbook).
Variatus (rep: 4889) Aug 24, '17 at 11:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login