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]

20409

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.