VB.NET Connecting with empty Excel cell - NullReferenceException was cought

Question

I have a problem while I'm connecting with Excel in VB.NET. When I'm looping through the cells to find last non-empty rows inside sheet, when it comes to moment that I'm comparing value of empty cell to "" I recieve error:NullReferenceException was caught. Object reference not set to an instance of an object."

There is a fragment of code:

   Dim xlApp As New Excel.Application
   Dim xlWb As Excel.Workbook
   Dim xlsheet As Excel.Worksheet
   Dim lRow As Integer = 1
   Dim excelFilePath As String

   Using FileDialog As New OpenFileDialog

        FileDialog.Title = "Please choose Excel file with XXX"
        FileDialog.FileName = ""
        FileDialog.ShowDialog()
        excelFilePath = FileDialog.FileName

    End Using

    xlApp.Visible = True
    xlWb = xlApp.Workbooks.Open(excelFilePath)
    xlsheet = xlWb.Sheets("Sheet1")

    Do While xlsheet.Cells(lRow, 3).value.ToString() <> ""
       lRow += 1
    Loop

I tried changing while condition many times to xlsheet.Cells(lRow, 3).value etc. but I always failed - the only way to make it working was by try - catch, but I'm wondering what I'm doing wrong and how to fix it in proper way?

`Do While xlsheet.Cells(lRow, 3) IsNot Nothing AndAlso xlsheet.Cells(lRow, 3).Value.ToString() <> ""` ? — Alex B., Jun 22 '16 at 07:20
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Blackwood, Jun 22 '16 at 19:50

41686d6564 stands w. Palestine · Answer 1 · 2016-08-25T15:37:57.237

Well, one thing you can do is to use String.IsNullOrEmpty, so the Do loop would look like this:

Do While String.IsNullOrEmpty(xlsheet.Cells(lRow, 3).value) = False
    lRow += 1
Loop

But that might get you into an infinite loop, so you might consider using For Next instead:

You should first get the last used row:

Dim lastRow As Integer = xlsheet.Cells(xlsheet.Rows.Count, 3).End(Excel.XlDirection.xlUp).Row

'Then use the loop:

For x = 1 To lastRow
    If String.IsNullOrEmpty(xlsheet.Cells(x, 3).value) = False Then
        'Do something
    End If
Next

And if your main purpose is to get the last used row, you don't have to loop through the rows. You can just use this line:

Dim lastRow As Integer = xlsheet.Cells(xlsheet.Rows.Count, 3).End(Excel.XlDirection.xlUp).Row

Hope that helps :)

score 0 · Answer 2 · answered Jun 22 '16 at 08:50

An empty cell in excel has a value of NOTHING

change your loop to as follows

    Do While xlsheet.Cells(lRow, 3).value IsNot Nothing
        lRow += 1
    Loop

The row you come out with is the first empty cell, lrow-1 is the last non empty cell in that column

When I'm looping through the cells to find last non-empty rows inside sheet,

In order to do this you would have to loop through the columns as well as the rows

VB.NET Connecting with empty Excel cell - NullReferenceException was cought

2 Answers2