On Sunday, February 10, 2013 6:55:32 PM UTC-8, Howard wrote:
I get a Type Mismatch error with this code.

Where I want to start at cell A8 to the last entry in col A and produce the results to col H, H8 and down.



Then code to copy paste special values H8 to end of column in place.



Sub ConcTestx() 'Type Mismatch error

Dim LastRow As Long

Dim Ws As Worksheet



Set Ws = Sheets("Sheet1")

LastRow = Ws.Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row)



Ws.Range("H8:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"

'paste special values in place here

End Sub



Thanks,

Howard

Thank you, Isabelle,

Works perfect!
I seem to always struggle to get away from Select Copy Paste Special Values.

Much appreciated

Regards,
Howard