Fill blanks in row 1 with nonblank values

[Jdw5511]

Hello,

I have values in row 1 that includes various blank cells throughout. I am needing to write a macro that fills blank cells with the value in the nonblank cell to the left. An example is shown below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Joe[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Justin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1350"]
<tbody>[TR]
[TD]In this example, the macro would fill the first two blank cells with the name Joe, and the next three with the name Justin, up until the last cell in the row.

I am assuming what I will need is to first find the last cell in row 1, then use a combination of IF(ISblank) and offset formula.

I am using excel 2010 and any assistance that could be provided would be greatly appreciated! [/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

Try:

Sub FillRowOneBlanks()

    Dim LC  As Long
    
    LC = Cells(1, Columns.count).End(xlToLeft).column

    With Cells(1, 1).Resize(, LC)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"
        .Value = .Value
    End With

End Sub

 

[Rick Rothstein]

Give this macro a try...

Sub FillBlanksAcross()
  On Error GoTo NoBlanks
  With Range("B1", Cells(1, Columns.Count).End(xlToLeft))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=RC[-1]"
    .Value = .Value
  End With
NoBlanks:
End Sub

 

[Jdw5511]

Works perfect, thank you!

 

[JackDanIce]

You're welcome