Array post to next empty row not working

[quitethenovice]

Hello friends

I have a project I am working on where my OutAry is not posting to the next empty row. My 2nd output is posting to row 4 and my 3rd output to Row 85 with big gap in between. My actual rows that appear are accurate but I cannot understand why the big blank space is created.

Would you mind having a look please?

My file is located https://filebin.net/2h7ysn90bl6o1odq/quitethenovice1.xlsm?t=upx3iahn

Below is the 3rd bit of code where it leaves the 40 rows of empty space:

ReDim OutAry(1 To LR1, 1 To 13)

For x = LBound(InAry) To UBound(InAry)
If InStr(InAry(x, 3), Type1) > 0 And IsEmpty(InAry(x, 5)) = False And _
(InStr(InAry(x, 5), "wo") = 0 And InStr(InAry(x, 5), "w/o") = 0 And InStr(InAry(x, 5), "wash") = 0 And _
InStr(InAry(x, 5), "sv") = 0 And InStr(InAry(x, 5), "s/v") = 0 And InStr(InAry(x, 5), "sluice") = 0) And _
IsEmpty(InAry(x, 7)) = False And InStr(InAry(x, 7), "unknown") = 0 Then

For t = LBound(TechArray) To UBound(TechArray)
If InStr(InAry(x, 17), TechArray(t)) > 0 Then GoTo NR3
Next t

r = r + 1
For y = 0 To UBound(ColAry)
OutAry(r, y + 1) = InAry(x, ColAry(y))
Next y
End If

NR3: Next x

wbrecords.Sheets("Missing").Range("A" & wbrecords.Sheets("Missing").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row).Resize(UBound(OutAry), 13).Value = OutAry

 

[Peter_SSs]

The issue is that the posted section of code ReDim's OutAry thereby emptying it (which is what you wanted I believe). However, the variable 'r' is not reset so continues to hold its value (about 40?) from the previous section of code. Therefore as values are now added to OutAry, they are added from about row 40 of the array. Hence, when the Array is written to the sheet, those first 40 empty rows of array values are also written. Try adding in that last section ..

ReDim OutAry(1 To LR1, 1 To 13)
r = 0
    For x = LBound(InAry) To UBound(InAry)

It may be that you want it set to 1 not 0 if you want a blank row between sections. You may also need to have a similar line of code in the earlier section.

 

[quitethenovice]

@Peter_SSs thank you Sir. That makes a lot more sense now and works for me!

 

[Peter_SSs]

Cheers. Thanks for the confirmation.