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?

score 0 · Answer 1 · answered Apr 30 '22 at 17:02

0

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.

answered Apr 30 '22 at 17:02

Antidiscrete

69
7

To clarify, cells A-J may not always be completely populated. Most of the time they will be, but occasionally any one or more of these cells (including A) may be blank. The first row where all 10 of these cells (A-J) are blank will be the first available row where I would like to insert data. Hence checking each cell to verify it is blank/empty. – jambutton Apr 30 '22 at 18:23
Understood - in this case, just check the column of the `Range` that `xlleft` takes you to, and confirm it is 1. – Antidiscrete Apr 30 '22 at 18:59

score 0 · Answer 2 · edited May 07 '22 at 12:43

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

Get row with first 10 empty cells in excel spreadsheet

2 Answers2