Excel VBA code to replace empty cell with adjacent (column) cells

Question

I have an Excel file where some of the column information is blank. I want to fill the blank cells with information from the two columns that come after the empty cell. For instance:

Column E is the full name of a person. Column F is their first name, Column G is their last name.

So, if E is blank I want to combine the contents of F and G into E.

1 JOHN JONES  | JOHN  | JONES
2             | MARY  | FORD
3 FRED MURPHY | FRED  | MURPHY
4             | TONYA | FARMER

I want the code to populate the empty cell in row 2 with 'MARY FORD' and row 4 with 'TONYA FARMER'.

I'm using Excel 2010 and have tried different combinations but nothing seems to work the way I want it to.

Answer 1

For Each rw In UsedRange.Rows
  If rw.Columns("E") = "" Then
    rw.Columns("E") = rw.Columns("F") & " " & rw.Columns("G")
  End If
Next rw

Answer 2

I think you are going to need a 4th column

because you need a formula or something in the blank space - however you have got text in there so you can have one or the other

if you had it like this

1 JOHN JONES  | =IF(A2="",CONCATENATE(C2," ",D2),A2)   | JOHN  | JONES  |
2             | =IF(A3="",CONCATENATE(C3," ",D3),A3)   | MARY  | FORD   |
3 FRED MURPHY | =IF(A4="",CONCATENATE(C4," ",D4),A4)   | FRED  | MURPHY |
4             | =IF(A5="",CONCATENATE(C5," ",D5),A5)   | TONYA | FARMER |

You could always hide that first column if you wanted?

This unless you want a function to run as a macro as a one time run to fill in the blanks?

Rob