CODEWITHSUNDEEP
vbaexcel
Andrei Vasilev
Andrei Vasilev

Reputation: 607

How can I convert all empty cells in a selection to have a value of NULL?

I have a Selection which could be any random range , e.g. ("A1:O10")

I need to make sure that all cells within this range that are blank get a value of NULL.

How can I do this ?

For Each c In Selection.Cells
        If Abs(c.Value) = "" Then c.Value = "NULL"
Next

Upvotes: 1

Views: 265

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

Edit: was proposed as an answer, but leaving this here as how not to do it (based on simoco's observation about SpecialCells and UsedRange)

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Value = "NULL"
On Error Goto 0

Fixed for SpecialCells "otchas:

  • SpecialCells only works within the sheet's UsedRange (even if you've selected cells outside that range)
  • SpecialCells doesn't work with a single-cell selection: it will automatically expand to the full sheet in this case

Assuming a single-area selection:

With Selection
    With .Cells(.Cells.Count)
        If Not .HasFormula And Len(.Value) = 0 Then
            .Value = "NULL"
        End If
    End With
    If .Cells.Count = 1 Then Exit Sub  
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).Value = "NULL"
    On Error GoTo 0
End With

From Siddharth Rout: one more way which doesn't use looping

Sub Sample()
    With ActiveSheet
        '~~> How like is that the bottom right cell is filled????
        '~~> Excel 2007+ - XFD1048576
        '~~> Excel 2003 - IV65536
        .Cells(.Rows.Count, .Columns.Count) = "A"

        If Selection.Cells.Count < 2 Then
            If Selection.Value = "" Then Selection.Value = "NULL"
        Else
            On Error Resume Next
            Selection.SpecialCells(xlCellTypeBlanks).Value = "NULL"
            On Error GoTo 0
        End If
        .Cells(.Rows.Count, .Columns.Count).ClearContents
    End With
End Sub

Upvotes: 5

Netloh
Netloh

Reputation: 4378

This should do the trick

Sub BlankToNull()
    For Each cell In Selection
        If Not IsError(cell.Value) Then
            If cell.Value = vbNullString Then cell.Value = "NULL"
        End If
    Next cell
End Sub

Upvotes: 3

Related Questions