When does Excel's Range object's Text property return Null

[Sathyaish]

Was writing a function to determine whether a particular row in an Excel spreadsheet was empty or not. My spreadsheet has 21 columns labled. For me, if the 22nd column contains any text I wouldn't care as long as the other columns contain text. I wanted to detect if all of those 21 columns were empty. Here's my function.

Public Enum SpreadsheetColumns
    EquipmentID = 0
    Supplier = 1
    Manufacturer = 2
    ModelNumber = 3
    LocationDept = 4
    SerialNumber = 5
    AcquisitionTerms = 6
    LeaseEndDate = 7
    PurchaseDate = 8
    PurchasePrice = 9
    Depreciation = 10
    IsColorDevice = 11
    PagesPrintedPerMonth = 12
    AmountSupplies = 13
    AmountServices = 14
    CPPTotal = 15
    CPPSupplies = 16
    CPPService = 17
    NumInvoices = 18
    ApplicationData = 21
End Enum



Public Function GetColumnAlphabetFromIndex(ByVal ZeroBasedColumnIndex As SpreadsheetColumns) As String

If ZeroBasedColumnIndex < 0 Or ZeroBasedColumnIndex > 21 Then Exit Function

    GetColumnAlphabetFromIndex = Choose _
    (ZeroBasedColumnIndex + 1, _
    "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V")
        
End Function


Public Function IsRowEmpty(ByVal RowNumber As Long) As Boolean

Dim StrFromCell As String
Dim StrToCell As String
    
    StrFromCell = GetColumnAlphabetFromIndex(SpreadsheetColumns.EquipmentID) & CStr(RowNumber)
    StrToCell = GetColumnAlphabetFromIndex(SpreadsheetColumns.ApplicationData) & CStr(RowNumber)
    If IsNull(Sheet1.Range(StrFromCell & ":" & StrToCell).Text) Then
        IsRowEmpty = True
    Else
        IsRowEmpty = Trim(Sheet1.Range(StrFromCell & ":" & StrToCell).Text) = vbNullString
    End If
    
End Function

Why does the line:

Sheet1.Range(StrFromCell & ":" & StrToCell).Text

return a value Null in my code when I actually have data in the row?

[adocwra]

you could try this


For Each c In Sheet1.Range(StrFromCell & ":" & StrToCell)
If c.Value <> "" Or c.Value <> vbNullString Then
IsRowEmpty = False
Exit For
Else
IsRowEmpty = True
End If
Next

[Sathyaish]

That was what I'd done earlier, but you see, if

c.Value

returns Null, then VBA won't even evaluate it and will just give up with a bloated Err object. So there's not much you can do to find out if its vbNullString. Besides your check

c.Value = "" Or c.Value = vbNullString

is more or less redundant.

However, some friends at another forum helped me with three working solutions. I think it was Code Guru or Extreme Visual Basic.

Thanks for your time, friend.

[adocwra]

why dont you post the solution?

[Sathyaish]

Sure

[adocwra]

my solution works just as well and if the there is text in cell one it doesnt check anything else
I added is vbnull
because you had it in there
but what a good forum for getting help
I have only just joined and most people are thankfull for us taking the time to look into it