Detect if all array elements in row are empty

Question

So im trying to write some VBA for my excel spreedsheet.

I have a table with four coloumns, i read each line and I want it to skip that line if coloumn 1 2 or 3 is empty, but instead it exits and doesnt move to the next line.

  For y = LBound(myArray) To UBound(myArray)
    
    If myArray(y, 1) = "" Then Exit For
        If myArray(y, 2) = "" Then Exit For
            If myArray(y, 3) = "" Then Exit For
            
            
    Attribute = "Animal"

    Value = myArray(y, 3)
    Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)
    
    
    Debug.Print Value

  Next y

Debug.Print "***TEST FINISHED***"

VBA lacks a continue statement. Fixing that lack is one of the relatively rare valid uses of `GoTo`: http://stackoverflow.com/a/8680815/4996248 — John Coleman, Jun 19 '16 at 13:02
Or you can use this if (myArray(y,1) & myArray(y,2) & myArray(y,3)) <> "" then do something. — Jules, Jun 20 '16 at 05:13

score 4 · Answer 1 · answered Jun 19 '16 at 13:31

4

Why not make it simple by using OR Try replacing your lines 2-4 conditions by 1 IF combined with AND

If myArray(y, 1) <> "" And myArray(y, 2) <> "" And myArray(y, 3) <> "" Then

answered Jun 19 '16 at 13:31

Shai Rado

33,032
6
29
51

score 1 · Answer 2 · answered Jun 20 '16 at 01:35

Running an ever increasing IF test to check every element will get messy.

Another option is to

Slice each row of the array using Index (in the example below I Transpose the array outside of the loop so Index slices a row rather than column)
Tranpose is used to convert the row slice to a 1D array
Join makes a string of the row
Len then tests if the row is empty (if the string is zero length)

code

`test array
myArray = [a1:d100]
myArr2 = Application.Transpose(myArray)

With Application
For y = LBound(myArray) To UBound(myArray)
    If Len(Join(.Transpose(.Index(myArr2, , y)))) = 0 Then
    `row is empty
        Else
    `plan b
    End If
Next y
End With

score 0 · Answer 3 · answered Jun 19 '16 at 14:45

Exit For attached to each of your conditions is explicitly terminating the For loop, not moving on to the next cycle of the loop. Excel VBA doesn't have a Continue -type option to skip to the next iteration of the loop, so you need to handle this yourself.

For readability I often code the failure conditions, as you have, then run the code on acceptable conditions under an Else clause:

For y = LBound(myArray) To UBound(myArray)
    
    If myArray(y, 1) = "" Or myArray(y, 2) = "" Or myArray(y, 3) = "" Then 
         ' unsuitable for action
    Else
            
        Attribute = "Animal"

        Value = myArray(y, 3)
        Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)
            Debug.Print Value

        ' TODO: take useful action on the generated string
 
    End If

Next y

    Debug.Print "***TEST FINISHED***"

Because it's not descriptive and can be confused with the cell property, I wouldn't have a variable called Value, either.

score 0 · Answer 4 · answered Jun 19 '16 at 15:04

Here is an idea you might use: What I'm doing is adding an if that only executes if none of the three lines are empty. If any of them is empty, the for-loop won't do anything and just skip to next y.

For y = LBound(myArray) To UBound(myArray)

If not(myArray(y, 1) = "" or myArray(y, 2) = "" or myArray(y, 3) = "") Then 
    Attribute = "Animal"

    Value = myArray(y, 3)
    Value = myArray(y, 2) & "¦" & FoodFunc(Value) & "¦" & myArray(y, 4)


    Debug.Print Value
End If
Next y
Debug.Print "***TEST FINISHED***"

Detect if all array elements in row are empty

4 Answers4