Replace Blank/empty cell with a string value

Question

Hope someone can help. I need to populate any blank/empty cells within a range of cells with a specific string. I also don't know that the last row would be in that range so I am looking for that first, I know that line of code works as I have used it for another function within the script. Below is the code I am using: -

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For Each r In Range("AS22:AU" & LastRow)
        If r.Value2 = vbNullString Then
            r.Value2 = "Greens"
        End If
    Next

I don't seem to get any compile errors when I run the code, it just does not work, I still have the same blank/empty cells.

Can anyone shed any light onto what I am doing wrong?

Answer 1

You could just use the .Replace Method with the range. It would be much quicker.

Range("AS22:AU" & LastRow).Replace "", "Greens", xlWhole

Answer 2

How about:

Sub dural()
    Dim r As Range, LastRow As Long

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For Each r In Range("AS22:AU" & LastRow)
        If r.Text = "" Then r.Value = "Greens"
    Next r
End Sub

This will change:

• truly empty cells
• cells containing formulas returning Null
• cells containing Null character as a constant