Help with limiting VBA range

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.

Code:

---

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

---

Can you please post a workbook with sample data

Edit: What version of Excel are you using?

Attachment 20409

thanks for your reply. I use Excel 2007. You can see the attachment here.

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

Code:

---

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

---

Also, your "Counts" should presumably refer to Row 2, not Row 1 where there is no data.

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

Code:

---

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

---

Thanks a lot, Mdmackillop. it works now without deleting the rest of the data.