Get row with first 10 empty cells in excel spreadsheet

Question

I am creating a vb.net application to update an excel spreadsheet. I access the excel file using Imports Microsoft.Office.Interop.

So far I can add data to the desired worksheet using hardcoded cell co-ordinates, for example :

sheet.Cells(3, 3) = mystring

I need to loop through each row to find the first row where each of the first 10 cells (A-J) contain no data so I can update those cells. I need to do it this way as columns K onwards may contain other data so I cant check for whole blank rows.

My attempt has started off just checking cell A in each row to begin with, trying to identify a blank/empty cell. If it worked I was thinking about using a for loop inside the do while loop too move along the cells in the row.

Using the following code I get a message box stating "System.__ComObject".

Dim rowcount As Integer = 0
Dim emptyrowfound As Boolean = False

Do While emptyrowfound = False
    rowcount += 1 
    MessageBox.Show(sheet.Cells(rowcount, 1).ToString) ' attempt to view cell contents for testing purposes
    If sheet.Cells(rowcount, 1).ToString = "" Then ' attempt to test if cell is blank/empty
        emptyrowfound = True
    End If

Loop

Once working I intend to apply cell updates like :

sheet.Cells(rowcount, 3) = mystring
...

Can anyone suggest a better way of checking and getting the row number?

Answer 1

First, I would do my check by starting in the 10th column and working left for each row using a Range object. You can use Range.End(xlleft) to check all cells to the left of the specified cell - it will stop at the first nonempty cell, which you are expecting to be in the first column. You should then be able to use the Range.Row property to return the row number of the cell you desire.

Answer 2

Below is a code snippet I dug up, hope it is useful.

For iRow = 1 To 5
   For iCol = 1 To 10

      IsFist10ColEmpty = True
      Cellval = oxlsworksheet.Range(oxlsworksheet.Cells(iRow, iCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Value

      If Len(Cellval) > 0 Then
         IsFist10ColEmpty = False
         Exit For
      End If
   Next

   If IsFist10ColEmpty = True Then
      MessageBox.Show(iRow & "Rows's First 10 cols are empty.")
   End If
Next

working line for you is : Cellval = oxlsworksheet.Range(oxlsworksheet.Cells(iRow, iCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Value

Originally I got answer from Anders Lindahl link is :

Anders Lindahl's original answer