Next Empty in Array

[adeel1]

Hi All

is there way how can I get last empty row in array as in pic yellow highlighted

just for example i am filling array with below code

array row can b different

Code: Select all

Sub Array_Empty()
Dim b() As Variant
ReDim b(1 To 10, 1 To 10)
'For i = 1 To 3
'here is some Conditons will Apply then will tell  number
 b(1, 1) = 1
 b(1, 2) = 2
 b(1, 3) = 3
 b(1, 4) = 4
 b(1, 5) = 5

 b(2, 1) = 1
 b(2, 2) = 2
 b(2, 3) = 3
 b(2, 4) = 4


'x = b(UBound(b(1)))
'Next i
End Sub

SS.png

Adeel

[HansV]

I guess you want the FIRST empty row, not the LAST one.

Do you want to know this row for each column separately, as indicated in your screenshot?
Or do you want to find the first row that is empty in all columns?

[adeel1]

yes first empty row , no for each column, I mentioned two for as example in loop it will be one.

let say I want to put value in b(find,8) >>Find mean first empty row


Adeel

[HansV]

Your example, by the way, shows the first empty column, not the first empty row: in b(1, 6), 1 is the row number and 6 is the column number.

Code to find the first empty row:

Code: Select all

    Dim r As Long
    Dim c As Long
    Dim f As Boolean
    For r = LBound(b, 1) To UBound(b, 1)
        f = True
        For c = LBound(b, 2) To UBound(b, 2)
            If Not IsEmpty(b(r, c)) Then
                f = False
                Exit For
            End If
        Next c
        If f Then Exit For
    Next r
    If r > UBound(b, 1) Then
        MsgBox "No empty rows!", vbExclamation
    Else
        MsgBox "First empty row is " & r, vbInformation
    End If

[adeel1]

Thx for your help, I think I did not explain correctly and well. I already afraid to post issues in different forum due to my bad English's.
I can understand good explanation is most important.
I really appreciate your help in this regard.

Adeel

[HansV]

Don't worry, English is not the first language for many members of Eileen's Lounge (me included). Don't be afraid to ask questions!