Concatenate dynamic range

[wpryan]

Hi All,

I want to concatenate the text from two separate columns, with a space between the text. The columns are dynamic ranges, but the number of rows will always be the same. The part of the code I'd hoped would do it this are:

[INDENT]Dim LR As Long
LR = Range("A:Q").Find(what:="*", LookIn:=xlValues, _
             SearchDirection:=xlPrevious).Row
Range("Q2:Q" & LR) = Range("M2:M" & LR) & " " & Range("L2:L" & LR)
[/INDENT]

I keep getting a "type mismatch" error on the last line.
...any suggestions? thanks in advance.

 

[AlphaFrog]

Try something like this...

    Dim r As Long
    For r = 2 To Range("A:Q").Find(what:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        Range("Q" & r) = Range("M" & r) & " " & Range("L" & r)
    Next r

 

[AlphaFrog]

This could work as well...

        Dim LR As Long
        LR = Range("A:Q").Find(what:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        Range("Q2").Formula = "=M2 & "" "" & L2"
        Range("Q2").AutoFill Destination:=Range("Q2:Q" & LR)
        Range("Q2:Q" & LR).Value = Range("Q2:Q" & LR).Value

 

[wpryan]

That's fantastic, worked like a charm. Thanks!