Loop that takes a long time

Dim lRw As Long, x As Byte

x = 2
lRw = Cells(Rows.Count, "A").End(xlUp).Row

With Range("D" & x & ":D" & lRw)
    .Formula = "=F" & x & "&" & "G" & x
    .Value = .Value
End With

Originally Posted by AB33

The OP does not use column A or D. Columns F-H

Oops... Thanks for the correction

I just tested it by creating a small data and forgot to change it to actual references

Here is the corrected version of code

Dim lRw As Long, x As Byte, sCol As String, sC1 As String, sC2 As String

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

x = 2
sCol = "H"
sC1 = "F"
sC2 = "G"

With Range(sCol & x & ":" & sCol & lRw)
    .Formula = sC1 & x & "&" & sC2 & x
    .Value = .Value
End With

Originally Posted by batchjb69

Thanks what if I wanted a space between the two values

Add a space within the & like the below

.Formula = sC1 & x & "& " & sC2 & x

Why not just use a standard function in Excel rather than VBA
i.e. in H2 copied down

Formula:

=F2&" "&G2

Or if you need the macro equivalent

Sub AddFormula()
   Range("H2:H30000") = "=F2&""""&G2"
End Sub

Avoid loops at all costs since they just take too long when compared to using standard functionality.