Exit loop and continue to next line when first empty cell is found

Question

I need to be able to loop through an Excel worksheet and at the first null or empty value I'd like the loop to exit and to continue to the next line.

                'Add the rows'
                        For Each cell In worksheetRow
                            If cell.Value Is Nothing Then
                                For index = _dataTable.Rows.Count - 1 To 0
                                    Dim deleteRow = _dataTable.Rows(index)
                                    If _dataTable.Rows.Contains("NULL") Then
                                        deleteRow.Delete()
                                    End If
                                Next
                                GoTo executeInsert
                            Else
                                row(cell.Start.Column - 2) = cell.Value.ToString.Trim()
                            End If
                        Next cell
                    Next

executeInsert:          Try
                            Company.Applications.ProductionEngine.BusinessAccess.BankTargetsConfigurationBusinessAccess.ExecuteLoadFileData(_dataTable, _year)

The problem is that the For Each loops through each cell. So on the first empty cell it finds, it proceeds onto the next empty cell and so on.

This is the result that is inserted into the table.

New Table

Up until row 13, at the first null occurence, I want to exit the loop and go to my next line of code which is responsible for saving what the loop gathered so far which is up until the far right cell with value '2018' on row 12.

For the sake of completeness, this is the next block of code that connects to the database and saves the data. This is where I need to go after I find the first null occurence.

                    Try


Company.Applications.ProductionEngine.BusinessAccess.BankTargetsConfigurationBusinessAccess.ExecuteLoadFileData(_dataTable, _year)
                    Catch ex As Exception
                        'The data insert failed'
                        InformationBox1.ShowErrorMessage("An internal error occured. Please refresh the page and try again.")
                    End Try
                    InformationBox1.ShowSuccessMessage("Data Uploaded")

I have tried this and it works. Problem is if something is added to the worksheet later and the rows become 148, this won't help.

                    For rowNumber As Integer = 1 To 147
                        Dim worksheetRow = _worksheet.Cells(rowNumber, 2, rowNumber, 4)
                        Dim row As DataRow = _dataTable.Rows.Add()
                        For Each cell In worksheetRow
                            row(cell.Start.Column - 2) = cell.Value.ToString.Trim()
                        Next
                    Next

I have also tried this but the rows don't increment. Each row from the worksheet is read and added to the first row, each record overwrites the previous one, so here there is always one row.

                   Dim rowCounter As Integer = 0
                    While rowCounter < _worksheet.Dimension.End.Row
                        Dim worksheetRow = _worksheet.Cells(1, 2, _worksheet.Dimension.End.Row, 4)
                        Dim row As DataRow = _dataTable.Rows.Add()
                        For Each cell In worksheetRow
                            If cell.Value IsNot Nothing Then
                                row(cell.Start.Column - 2) = cell.Value.ToString.Trim()
                            End If
                            rowCounter += 1
                        Next
                    End While

And finally, I've tried a Do Loop. I get the same result as the While Loop above.

                    Do
                        Dim worksheetRow = _worksheet.Cells(1, 2, _worksheet.Dimension.End.Row, 4)
                        Dim row As DataRow = _dataTable.Rows.Add()
                        For Each cell In worksheetRow
                            row(cell.Start.Column - 2) = cell.Value.ToString.Trim()
                        Next
                    Loop Until _worksheet.Cells.Value = ""

For any clarifications please let me know.

[`If yourdata = "" Then Exit For` ?](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/exit-statement) — ashleedawg, Jun 21 '18 at 06:14

Samuel Hulla · Answer 1 · 2018-06-21T07:54:56.590

0

It's kind of difficult to understand what exactly you're trying to achieveso I'm having to go with the presumption your variables such as worksheet row are correctly defined...

Also, you have an error in your code. .Start is not a property of a range object (cell.Start.Column - 2) in your code. This will result in an error I can't correct given you haven't provided your input data nor expected result.

I presumed you're trying to offset the column by 2 to the right, but change it accordingly to what you're trying to achieve

Same goes for .ToString which is not a Range object method in VBA. Unless you have them somewhere defined in your own class properties and have not included it in your code here. Just like with offset, sounds like you're trying to utilize the CStr() function here, but once again, with no expected output I can only be guessing here.

Either way, those are small potential errors you should be able to take care of yourself. In general, your loop should look something like this:

For each cell in worksheetRow
    If IsEmpty(Cell) Then
        Exit For ' this will exit the entire for loop after reachign an empty cell
    End If
    row.Offset(0, -2) = CStr(Trim(cell.Value))
Next cell

In case you want to skip empty cell, instead of exiting the entire for loop, use a secondrary dowhile loop in your cell that would act as an continue - example found in this stack question: Continue For loop

edited Jun 21 '18 at 07:54

answered Jun 21 '18 at 07:50

Samuel Hulla

6,617
7
36
70

I'll run it shortly and let you know. – Nikos Jun 21 '18 at 07:51
@NikosV keep in mind, the code I provided needs to be adjusted to what you're trying to achieve. The most important thing is to utilize the `Exit For` statement with `IsEmpty()` comparison. Rest of the output depends on how you define it – Samuel Hulla Jun 21 '18 at 07:52
Doesn't work. It inserts the null values too. After debugging I see that the program goes to the null cell and then finds that its null, which makes sense, when it sees the the cell is null, it exits the for and goes to the insert. I don't want the program reading null but that can't happen since the system won't know it's null unless it sees it. I thought about storing the whole thing in an array list and then removing "NULL" values before the insert. How does that sound? – Nikos Jun 21 '18 at 08:07
Or better even, before the insert, remove rows with null in them from the datatable. Is that doable? I'll look into something like that. – Nikos Jun 21 '18 at 08:11
@NikosV just so we're clear, are you trying to exit on empty cells or on cells with value "NULL" inside of them, or cells that have resulted in a #NULL! after an operation? These are not the same thing in VBA – Samuel Hulla Jun 21 '18 at 08:13
I want to exit after the last non empty cell. As per the photo, I'd like to exit as soon as it reads row 12. – Nikos Jun 21 '18 at 08:20

score 0 · Accepted Answer · answered Jun 21 '18 at 08:49

Did it guys. Just needed to delete the particular row if it contained Nothing.

Here's the final code.

                For rowNumber As Integer = 1 To _worksheet.Dimension.End.Row
                    Dim worksheetRow = _worksheet.Cells(rowNumber, 2, rowNumber, 4)
                    Dim row As DataRow = _dataTable.Rows.Add()

                    For Each cell In worksheetRow
                        If cell.Value Is Nothing Then
                            row.Delete()
                            GoTo executeInsert
                        Else
                            row(cell.Start.Column - 2) = cell.Value.ToString.Trim()
                        End If
                    Next cell
                Next

//executeInsert is the connection/insert data method

Thanks all for your help.

I'll accept the answer in two days (I'm not allowed now) hopefully to help others with the same problem. — Nikos, Jun 21 '18 at 08:50

score -1 · Answer 3 · answered Jun 21 '18 at 06:40

-1

I am not sure, but maybe you could just do something like:

For Each cell In rng
    ' do some stuff
    If myCondition Then GoTo exitFor
 Next cell
 exitFor:

answered Jun 21 '18 at 06:40

Oskar

108
9

I'll run it now and let you know. – Nikos Jun 21 '18 at 06:41
1

Just ran it and does the same as shown in the table. – Nikos Jun 21 '18 at 06:46
Basically what it's algorithm is 1. Found null cell. 2. Go to next cell 3. Cell is null. 4. Go to next cell. 5. Found a number (we don't want this number(468793...)). 6 Insert the number. 7. Insert '2018'. 8. Change rows. All I need is for it to stop iterating after number 1. We don't want to get to number 2. – Nikos Jun 21 '18 at 06:52
I've added a Goto if it finds a null cell telling it to go to the insert straight away. I'll run that too see how it goes. Basically where you wrote exitFor I put executeInsert and added the tag at the insert line. – Nikos Jun 21 '18 at 07:17
Just be carefiul with the GoTo expressions as they can make code hard to understand ;) – Oskar Jun 21 '18 at 07:19
Ran it. Updated the code too. Now it inserts just one row with nulls and not three. Check new photo. – Nikos Jun 21 '18 at 07:24

Exit loop and continue to next line when first empty cell is found

3 Answers3