Mati44
09-20-2017, 05:19 AM
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.
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. I would like to be able to define the limits of the output range as I can do for input range. thanks.
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
Next
Cells(R, C).Value = Mid(Txt, 2)
Next
Next
End Sub
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. I would like to be able to define the limits of the output range as I can do for input range. thanks.
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
Next
Cells(R, C).Value = Mid(Txt, 2)
Next
Next
End Sub