Get row with first 10 empty cells in excel spreadsheet-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

I have workout for you, consider if it is useful for you. see the code :

     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