I have a Global variable which shows as a Variant/Variant(1 to 33, 1 to 9) in the Locals window. So it's a 2D array, yet when I try to for each over the array I cannot access the whole arrays only the cell values
Public myRows As Variant
Public myTable As ListObject
Sub SendEmails()
Dim X As Long
Dim Row As Variant
SetMyTable
For Each Row In myRows
Debug.Print CheckRow(Row)
Next Row
End Sub
EDIT: Adding the CheckRow function
Function CheckRow(Row As Variant) As Boolean
Dim IsRowValid As Boolean
IsRowValid = True
If IsEmpty(Row(1)) = True Then
IsRowValid = False
End If
If IsEmpty(Row(2)) = True Then
IsRowValid = False
End If
If IsEmpty(Row(3)) = True Then
IsRowValid = False
End If
If IsEmpty(Row(4)) = True Then
IsRowValid = False
End If
If IsEmpty(Row(5)) = True Then
IsRowValid = False
End If
CheckRow = IsRowValid
End Function
Public myRows As Variant
'Public myTable As ListObject ' irrelevant
Sub SendEmails()
'SetMyTable ' don't know what that does
Dim r As Long
For r = 1 To UBound(myRows, 1) ' loop through the rows
Debug.Print CheckRow(myRows, r)
Next r
End Sub
Function CheckRow(ByVal Data As Variant, ByVal RowIndex As Long) As Boolean
Dim c As Long
For c = 1 To UBound(Data, 2) ' loop through the columns
' Note that 'CheckRow' is initially (by default) equal to 'False'.
If IsEmpty(Data(RowIndex, c)) Then Exit Function
Next c
CheckRow = True ' all values in the row are not empty
End Function
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments