CODEWITHSUNDEEP
vbaexcelvbscript
user3088476
user3088476

Reputation: 155

Replace Blank/empty cell with a string value

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?

Upvotes: 5

Views: 16657

Answers (3)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

D_Bester
D_Bester

Reputation: 5901

Use IsEmpty() to test for blanks. 

    If IsEmpty(r.Value2) Or Trim(r.Value2) = "" Then
        r.Value2 = "Greens"
    End If

Also watch out for cells with a zero length string or a space. They appear empty but in fact are not.

Upvotes: 0

luke_t
luke_t

Reputation: 2975

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

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

Upvotes: 3

Related Questions