Find Index of blank entry in a 2D Array Excel VBA

Question

I have an 2D Array Dim Line_Qty(6, 2) with values in it like the following:

**Date      Line_No  Quantity**

2016-11-15   1       5856

2016-11-15   2       5451

2016-11-15   3       5499

2016-11-15      

2016-11-15      

2016-11-15

What i want to find is the index of the first blank which is Line_Qty(4, 1)

the purpose of this is that i am pasting these values in excel sheet and i want to stop pasting till the last entry of Quantity.

my pasting code is this

For i = 0 To 6
For j = 0 To 2

Worksheets("DY_SET").Cells(i + 1, j + 1).Value = Line_Qty(i, j)
Worksheets("DY_SET").Range("A" & i + 2).NumberFormat = "yyyy-mm-dd"

Next j
Next i

Hope i made myself clear, and thanks in advance

yea, i want to exit both loops because i dont want just dates without the lineno and quantity. so loop should exit at last quantity value. — Tahmaseeb
– Tahmaseeb, Commented Nov 30, 2016 at 16:11

user3598756 · Accepted Answer · 2016-11-30 14:40:43Z

1

You could first "truncate" the array and then paste surviving values, like follows:

Dim Line_QtyCopy(0 to 6, 0 to 2) As Variant

' search Line_Qty first row with empty qty
For i = 0 To 6
    If Line_Qty(i, 2) = "" Then Exit For
Next i

With Worksheets("DY_SET").Range("A1") '<--| reference target range
    If i <7 Then '<--| if found empty qty before reaching array end
        i = i - 1 '<--| update row index to last not emoty one
        ReDim Line_QtyCopy(0 to i, 0 to 2) '<--| size a new array to the number of rows
        ' fill it up to last not empty qty in Line_Qty
        For i = 0 To i
            For j = 0 To 2
                Line_QtyCopy(i,j) = Line_Qty(i, j)
            Next
        Next
        .Resize(i).Value = Line_QtyCopy '<--| write it down from cell A1
    Else
        .Resize(6).Value = Line_Qty
    End If
End With

answered Nov 30, 2016 at 14:40

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tahmaseeb Over a year ago

thanks a lot, it works perfectly, i was thinking index point of view, thats why i was stuck, truncating the array wasnt even in my head. thanks a lot again.

Limak · Accepted Answer · 2016-11-30 14:02:48Z

1

This code escapes loop when some value of array is empty. Is that what you need?

For i = 0 To 6
    For j = 0 To 2
        If Line_Qty(i, j) = "" Then Exit For
        Worksheets("DY_SET").Cells(i + 1, j + 1).Value = Line_Qty(i, j)
    Next j
    Worksheets("DY_SET").Range("A" & i + 2).NumberFormat = "yyyy-mm-dd"
Next i

answered Nov 30, 2016 at 14:02

Limak

1,5173 gold badges13 silver badges22 bronze badges

1 Comment

Tahmaseeb Over a year ago

Thanks @Limak, i kinda mashed your answer with user3598756 to get my own answer. thanks for your help too :)

Collectives™ on Stack Overflow

Find Index of blank entry in a 2D Array Excel VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related