How to Make Cells "Blank," not "Empty" in VBA

1
votes

I using Labview to generate a Excel report that essentially pastes an array into the spreadsheet. There are gaps in the spreadsheet, for example:

1
2
3

1
2
3

But because I am inserting an array into the spreadsheet, the gaps are empty, but they aren't blank.

When I run vba code checking each cell using "IsEmpty," it returns true. But if I run an excel formula using "ISBLANK," it returns false. I have tried the following, but it doesn't make the cell blank.

If IsEmpty(Cells(r,c)) Then
    Cells(r,c).Value = ""
    Cells(r,c).ClearContents
    Cells(r,c) = ""

I want to make the cells blank without having to delete them. This is because I'm trying to use .End in my VBA code, but it doesn't stop at the gaps.

2
excelvba
remove the last line – GMalc
I would double check your results. I cannot think of a situation where IsEmpty would be True, but ISBLANK would be false. – GSerg
IsEmpty is a native VBA standard library function that returns True given a Variant/Empty, i.e. an uninitialized Variant variable. The fact that the value of a blank cell (no content, no formula) yields a Variant/Empty is a convenient coincidence: IsEmpty was never meant to be used to determine whether an Excel Range contains something. – Mathieu Guindon

2 Answers

3
votes

You don't need to check IsEmpty(), instead:

If Cells(r, c).Value = "" Then Cells.ClearContents

This will remove Nulls. By Nulls, I mean zero-length Strings.

1
votes

This might be overkill, but it'll work for you:

Sub tgr()

    Dim ws As Worksheet
    Dim rClear As Range
    Dim aData As Variant
    Dim lRowStart As Long
    Dim lColStart As Long
    Dim i As Long, j As Long

    Set ws = ActiveWorkbook.ActiveSheet
    With ws.UsedRange
        If .Cells.Count = 1 Then
            ReDim aData(1 To 1, 1 To 1)
            aData = .Value
        Else
            aData = .Value
        End If
        lRowStart = .Row
        lColStart = .Column
    End With

    For i = LBound(aData, 1) To UBound(aData, 1)
        For j = LBound(aData, 2) To UBound(aData, 2)
            If Len(Trim(aData(i, j))) = 0 Then
                If rClear Is Nothing Then
                    Set rClear = ws.Cells(lRowStart + i - 1, lColStart + j - 1)
                Else
                    Set rClear = Union(rClear, ws.Cells(lRowStart + i - 1, lColStart + j - 1))
                End If
            End If
        Next j
    Next i

    If Not rClear Is Nothing Then rClear.ClearContents

End Sub