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?

luke_t · Accepted Answer · 2016-03-02 12:13:27Z

4

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

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

answered Mar 2, 2016 at 12:13

luke_t

2,9874 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3088476 Over a year ago

@Iturner I did try that, but got the same results - Nothing. the cells stayed blanks. this is what I had originally LastRow = Cells(Rows.Count, 2).End(xlUp).Row With Range("AS22:AU" & LastRow) .Replace what:="", Replacement:="On Track" End With

luke_t Over a year ago

Are there formulas within the cells which are causing the cells to show as blank? Have you debugged to ensure that the LastRow variable is returning the expected integer?

user3088476 Over a year ago

@Iturner I will just run LastRow and check that it is returning a value

user3088476 Over a year ago

@Iturner Thanks I checked this and wasn't getting the correct cell number, I changed the column I was running LastRow on and I now get the results I wanted. - Thanks

Gary's Student · Accepted Answer · 2016-03-02 12:35:15Z

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

edited Mar 2, 2016 at 12:35

answered Mar 2, 2016 at 12:18

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

5 Comments

user3088476 Over a year ago

this does not work either, the cells stay blank - Any clues as to what this is

Gary's Student Over a year ago

@user3088476 I suspect the 2 in the equation for LastRow ............are you sure you want column B to control the end of the loop ??

user3088476 Over a year ago

The column B is a column I know I will contain a value in the last row, the column AS - AU which I am copying in may not contain a value within the last row, this is why I am searching on column B.

Gary's Student Over a year ago

@user3088476 Perhaps the cells in AS:AU only appear empty.................. perhaps they contain a single space ??

user3088476 Over a year ago

@ Gary's I have it working now - thanks for you help and input

D_Bester · Accepted Answer · 2016-03-02 12:13:59Z

0

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.

answered Mar 2, 2016 at 12:13

D_Bester

5,9916 gold badges43 silver badges82 bronze badges

Collectives™ on Stack Overflow

Replace Blank/empty cell with a string value

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related