Consolidate multiple cell values into one variable

[Drakken]

Hello!

I am trying to pull data from one worksheet and move it to another worksheet. The first worksheet has 23 columns of data. I want to combine all of the data within these columns (focusing on only one row at a time) into one variable and then transfer that consolidated data to another worksheet where it will be further used. To differentiate between the columns, I planned to use a semicolon between each column value. Also, I do not know if it will matter but the column values are made up of either text or numbers. I know how to move data between worksheets, but not sure on how to consolidate all of this data. If someone could show me how to even consolidate a few columns, I could easily build off of the concept.

I thought about loading the data into an array for ease, but had no idea where to go from there to move the array data into one single string. At that point, I was thinking maybe it was an unnecessary step to do this. I also considered possibly using the concatenate spreadsheet function to help accomplish this, but this also seemed overly complicated and messy.

I have a very basic knowledge of how to utilize VBA, but I am unaware of all the functions and options available to me. If you could help me or point me in the right direction, that would be greatly appreciated.

Thanks!

 

[StephenCrump]

Welcome to the Forum.

Sorry, I'm not clear. What are you trying to achieve that a simple Copy/Paste of the data won't provide?

 

[Drakken]

Welcome to the Forum.

Sorry, I'm not clear. What are you trying to achieve that a simple Copy/Paste of the data won't provide?

Thank you Stephen!

Hmm.. maybe I was over thinking it? If that is the method I decided to use, how would you go about doing it? Can you copy and paste so that a single variable holds all of this data? That is the key thing I am trying to achieve. It has to be a single variable that holds all of the data. Also, it cannot be an array.

 

[StephenCrump]

It has to be a single variable that holds all of the data.

I'm still not clear on this requirement. Are you familiar with structured tables? Range names? Would either of these meet your requirement, which presumably is the ability of the new spreadsheet to look up the copied database?

 

[Drakken]

I'm still not clear on this requirement. Are you familiar with structured tables? Range names? Would either of these meet your requirement, which presumably is the ability of the new spreadsheet to look up the copied database?

I understand how to reference and use range names to pull data if that is what you are talking about. I will try to explain my set up furthermore and hopefully that helps to clear things up. Currently, a user will activate the macro via a button. First, they are instructed to select the range of data they want to transfer to the new spreadsheet. The number of columns stays constant, but the range can change depending on what the user selects. I then need to store every row selected top-down, column-by-column separated by a semicolon. At the end of the 23 columns, it should move to the next row until all data from the selected range is stored into the specified variable. I am currently attempting this with a while loop. I feel that this method may work but I will not be able to test it until tomorrow.

This information needs to be stored into a single variable because I am generating a QR code with this information. The current QR code spreadsheet I am using uses a single cell input, which is why I need all of my data to be consolidated.

 

[StephenCrump]

Ahh, that's clearer. Thanks for providing more context.

Have a look at the TEXTJOIN() function, which I think should be available in Excel 2016. Will that work for you?

 

[Drakken]

Thank you for the suggestion Stephen! Unfortunately, 2016 does not have the TEXTJOIN() function, but it did lead me to some useful information. There was an easy to follow VBA created TEXTJOIN() function I found on YouTube that I was going to implement, but I actually got my while loop to work properly! :D

It was a fairly simple solution, I just had to struggle to see that. I definitely appreciate your help though. I believe your input helped me think through the process and find the information I needed.

Anyway, here is the solution I ended up using to consolidate all of the data (just in case anyone else ever finds their self in a similar situation):

Dim qrData As Variant
i = 1
j = 1
    
Do While i < m ' Going through the # of rows selected
    Do While j < n ' Going through the # of columns
        If (j = 1) Then
            qrData = qrRows.Cells(i, j)
        End If
                      
        If (j > 1) Then
            qrData = qrData & ";" & qrRows.Cells(i, j)
        End If
        j = j + 1
        Debug.Print qrData
    Loop ' End of column loop
    j = 1
    i = i + 1
Loop ' End of row loop

"m" and "n" are set before hand by user selection

Thanks again, Stephen!