VBA - Excel cells populated with 1d array aren't blank

Question

I fill cells with 1d array defined as Variant. All 3 variables in this array are public string variables. Sometimes variable is VbNullString and goes as such into an array.

Unfortunately while selecting empty cells in Excel which are corresponding to VbNullString values, they are count as non blank and =IsBlank() function returns false. A check from VBA's immediate window doesn't detect anything in example cell:

?"check" & range("f4").Value & "character"

Result is: "checkcharacter"

How can I clear these cells to really be blank and change something in my macro not to populate them?

Dim results As Variant

results = Array(Company, Address, Phone)

With WS
    Range(.Cells(resultCounter, 1), .Cells(resultCounter, UBound(results) + 1)).Value2 = results
End With

check ?[f4] or ?[f4].Formula. =IsBlank() function returns false if the cell is not empty. what's the question? — Slai
I wonder how can I clear these cells to be blank and find a way not to populate these cells with my macro in the first place. IsBlank on these cells returns false as I've mentioned. — Ryszard Jędraszyk
ISBLANK() returns True for me on a cell which I set to vbNullString using VBA. It would help to update your question with a set of exact steps to reproduce your problem. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2016-11-07T01:36:10

Cannot reproduce the issue from your description of what you're doing:

Dim Company As String, Address As String, Phone As String

Sub Tester()

    Dim results As Variant

    Company = "CpmpanyA"
    Address = vbNullString
    Phone = "555-1212"

    results = Array(Company, Address, Phone)

    With ActiveSheet
        .Range(.Cells(20, 1), .Cells(20, UBound(results) + 1)).Value2 = results
    End With

    Debug.Print ActiveSheet.Evaluate("ISBLANK(B20)") '>> ###True###

End Sub

VBA - Excel cells populated with 1d array aren't blank

2 Answers