Results 1 to 5 of 5

Thread: Help with limiting VBA range

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    57
    Location

    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.

    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
    Last edited by Mati44; 09-20-2017 at 06:17 AM. Reason: Added code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •