CODEWITHSUNDEEP
excelvba
dboolean
dboolean

Reputation: 13

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

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.

Upvotes: 1

Views: 17895

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 4

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 1

Related Questions