Trying to repeat a VBA Macro on across each column

By wca
Feb 8, 2008
  1. I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now. Thank you for any help or advice. Dan

    Sub ConcatenateAll()
    Dim x As String, rng As Range, cel As Range
    With ActiveSheet
    Set rng = .Range("A1:A300")
    For Each cel In rng
    x = x & cel.Value
    .Range("A1").Value = x
    End With

    End Sub
  2. jobeard

    jobeard TS Ambassador Posts: 9,317   +618

    The output will need to be in some cell not part of the Concatenation.

    If Concat(Ax:Ax+300) then outputs might appear at
    Ax-1, Ax+301, or ANY other Column than A.

    HOWEVER, such wide columns will be very hard to use.
Topic Status:
Not open for further replies.

Similar Topics

Add New Comment

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...