I have this code that combines a range of cells into a single cell comma separated. For example A1:1 , A2:2, A3:3, A4:4 then they will be like (1,2,3,4) in A5. This code works on the whole worksheet. I need help to be able to adjust its range in the code. I can change the Row value, and Column start for the results as you can see in the code. thanks.

The problem I am having with this VBA, lets say if the data is in A:F and output cells is in the K:M, it deletes the other irrelevant data present in the cells after column M.

Sub GetValues()
  Dim R As Long, C As Long, V As Variant, Txt As String
  For C = 11 To Cells(1, Columns.Count).End(xlToLeft).Column
    For R = 5 To Cells(Rows.Count, "A").End(xlUp).Row
      Txt = ""
      For Each V In Split(Cells(1, C).Value, ",")
        If Not Intersect(Rows(R), Columns("A:F")).Find(V, , , xlWhole, , , False, False) Is Nothing Then Txt = Txt & "," & V
      Cells(R, C).Value = Mid(Txt, 2)
End Sub

This expression: Cells(1, Columns.Count).End(xlToLeft).Column takes the column loop out to the last entry in the first row, just as this expression: Cells(Rows.Count, "A").End(xlUp).Row takes the loop out to the last row in the first column.

It seems like this routine is just taking whatever list is in the first row of each column in range and checking to see if each element exists in the first 6 entries on the corresponding row, and copying over those elements that do - which is not exactly what you described. If no matches are found, yes, the cell is set blank.

Intersect(Rows(R), Columns("A:F")) would, in my opinion, be more clearly and economically expressed as the lead cell resized to the correct columns: Cells(R,1).Resize(1,6)


I got an alternative answer on Vbaexpress.com, here it is:

from AD1 or adjust this line to search from the Left of AD1, changed to S in this example

For C = 11 To Cells(1, "S").End(xlToLeft).Column 

BTW, if you have such issues try something like below to track down the issue

Cells(R, C).select 
 ' or
Cells(R, C).interior.colorindex = 6 
Cells(R, C).Value = Mid(Txt, 2) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.