Thread: Looping through columns on the same row, changing cell values to NULL

Hi,

How should the syntax go? I would like to look through a range first. And if the cells(target.row, 1).value = vbNullString then, I want all subsequent values on the same row in all columns to be "". There is a total of 26 columns.

code segment is: If I have 100 columns, then I would need to write 100 lines....which is not very efficient.

If Cells(Target.Row, 1).Value = vbNullString Then
    Cells(Target.Row, 2).Value = ""
    Cells(Target.Row, 3).Value = ""
    Cells(Target.Row, 4).Value = ""
    Cells(Target.Row, 5).Value = ""
    Cells(Target.Row, 6).Value = ""
    Cells(Target.Row, 7).Value = ""
    Cells(Target.Row, 20).Value = ""
    Cells(Target.Row, 21).Value = ""
    Cells(Target.Row, 23).Value = ""
    Cells(Target.Row, 24).Value = ""
    Cells(Target.Row, 25).Value = ""
    Cells(Target.Row, 26).Value = ""
    Cells(Target.Row, 12).Value = ""
    Cells(Target.Row, 13).Value = ""
    Cells(Target.Row, 16).Value = ""
    Cells(Target.Row, 17).Value = ""
    Cells(Target.Row, 18).Value = ""
    End If

Thanks for your input

Hi there,

Try this:

    
    If Cells(Target.Row, 1).Value = vbNullString Then
        Range(Cells(Target.Row, 2), Cells(Target.Row, 26)) = ""
    End If

Regards,

Robert