Merge 2 cells for Range

[Eclektics]

Hi All,

I'm trying to merge 2 lots of columns into 1 column at the end of the data to provide a look up reference. The first column contains staff ID and the second contains question ID.

My code is below and it just doesn't seem to work, I get a type mismatch but guides I've followed suggest the below code. Any help anyone can offer would be greatly appreciated. For reference, column BI will never have data on the sheet so I'm using it as a 'safe column' for the merge as I know will always be empty.

Dim dws as Worksheet
Dim LRow as long

Set dws = Sheets("Data")

LRow = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row

dws.Range("BI2:BI" & LRow) = Range(Cells(2, 4).Address(False, False), (Cells(2, 25).Address(False, False))).Merge

 

[Fluff]

How about

Sub MyConcat()
   Dim Lr As Long
   
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("BI2:BI" & Lr)
      .FormulaR1C1 = "=rc4&rc25"
      .Value = .Value
   End With
End Sub

 

[Eclektics]

Hi Fluff,

Thanks for the quick response. The code runs without error but nothing appears in column BI once it's ran.

 

[Eclektics]

Scratch that last response, it worked but I didn't reference the sheet I wanted it to work on.. whoops lol

Thank you so much for your help Are you able to quickly explain how it worked?

 

[Fluff]

This line

.FormulaR1C1 = "=rc4&rc25"

puts a formula into the entire column looking at col 4 & 25 for that row and this line

.Value = .Value

Then converts the formula to values