Help with limiting VBA range

[Mati44]

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

[mdmackillop]

Can you please post a workbook with sample data

Edit: What version of Excel are you using?

[Mati44]

Book1.xlsm

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

[mdmackillop]

Remove "Counts:" 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

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

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

[Mati44]

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