Chip,

Thanks, that worked to fill out the column, but it's placing the exact

formula in each cell, I need it to concatenate the info in each row. Is

there a way to have the formula update to the new row? This is the formula

that I input but I need the row to change to the next row as it fills out

down the range.

=IF(LEN(R4C1)<LEN(R4C5), CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))

I appreciate your help on the loop, that's one more step in the right

direction for me.

Chip Pearson said:

Try

Dim R As Range

Set R = Range("F4")

Do Until R.EntireRow.Cells(1, "A").Value = vbNullString

With R

.FormulaR1C1 = "your formula here"

With .Interior

.ColorIndex = 38

.Pattern = xlSolid

.PatternColorIndex = xlColorIndexAutomatic

End With

.Locked = True

End With

Set R = R(2, 1)

Loop

Cordially,

Chip Pearson

Microsoft MVP 1998 - 2010

Pearson Software Consulting, LLC

www.cpearson.com
[email on web site]

I am trying to write a macro that will do the following as long as there is

data in column A.

Range("F4").Select

ActiveCell.FormulaR1C1 _

= "=IF(LEN(R4C1)<LEN(R4C5),

CONCATENATE(R4C1,R4C5),CONCATENATE(R1C5,R1C1))"

With Selection.Interior

.ColorIndex = 38

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

Selection.Locked = True

End With

How do I write a Loop that will keep doing this on each successive row until

the cell in column A is blank?

.