Fill blank cells in column AH with value from column U (same row)

[fruela]

Hi guys,

I have this code that, for column X, fills all blanks with the value above. Range is same as W.

For column AH, I need to fill all blanks with the value from U, which is 13 columns to the left. (Example: AH4 = U4 ; AH20 = U20). Same range. Can you help me?

Cheers

Sub copyToBlanks1()
    With Range("X2:X" & Range("W" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
    End With
End Sub

 

[Joe4]

Does this do what you want:

    With Range("AH2:AH" & Range("W" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-13]"
        .Value = .Value
    End With

 

[fruela]

Yes, simple. Understood! Thanks Joe4

 

[Joe4]

You are welcome!

The trick is the "RC" part, which stands for row/column. The number in parenthesis after the R/C reference is the "offset" from that row column that the formula is being placed in.
So R[-1] means one row before the row that the formula is being placed in.
And C[-13] means 13 columns before the column that the formula is being placed in.