I have been attempting to figure out a formula to skip blank lines when I am referencing a list of parts for a bill of materials. The problem I've been running into is where the parts list is being held is updated based on what is selected and then transferred to the actual bill of materials there are blank spaces because some machines have more or less parts. I need to skip these and go to the next line and continue with the cells from there. So if I go from Cell if A1:A24 are filled and A25:A29 are blank and A30 has data I want to skip until it gets to A30 and continue on from there (A30:A35).
42 Answers
Let's pretend your data looks something like what's in column A, and the results you want are in column C:
In cell C2 and copied down is this formula:
=IF(ROW(C1)>ROWS($A$2:$A$11)-COUNTBLANK($A$2:$A$11),"",INDEX($A$2:$A$11,SMALL(INDEX(($A$2:$A$11<>"")*ROW($A$2:$A$11)-ROW($A$2)+1,),COUNTBLANK($A$2:$A$11)+ROW(C1))))Adjust the $A$2:$A$11 to suit your data. Make sure that in the -ROW($A$2)+1 portion, the $A$2 is the first cell of your data, even it is blank (the point being that data could be there, and that it's not a header, and it's the first cell the formula should look at).
Within the loop that you use to hop from cell to cell, put your code within this if statement:
If Not IsEmpty(yourCell) Then 'yourCell needs to be replaced
'your code goes here
End If
