hi Howard,

Sub ConcTestx()
Dim LastRow As Long
Dim Ws As Worksheet

Set Ws = Sheets("Sheet1")
LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row

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

isabelle

Le 2013-02-10 21:55, Howard a écrit :
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