Skip blank cells in a loop

I am converting an inventory from the form:

Item ID	#Small	#Medium	#Large...
1	4	5	6
2	8	9	10

to

Item ID/SIZE	#Available
1Small	4
1Medium	5
1Large	6
2Small	8
...

I have code that does this effectively.

Please Login or Register  to view this content.

However, if there are blanks in the table, my current code creates items with 0 available. I would like it to skip blanks and only create a new item if there is a non-zero quantity of that size for that item.

I think you're right that that's the strategy I should use. When I ran that code, it deleted all my data . I think it's offset incorrectly.

Avoid using ActiveCell.
Are you trying to loop through a range of non blank rows? It may be easier to resolve it if you could attach a sample.

Sample.xlsm

This is the first macro I've written, so I'm up for learning better techniques. How should I write it to avoid using ActiveCell?

I attached a sample with made-up data. The first sheet is the before data and the second sheet is my desired result.

The code is not looping through a range rather, looping through an array. The result is in new sheet created by the code itself.

Thanks. That works perfectly. I'll spend some time looking through your code to figure out what you did, since it looks like a cleaner way than I took.

Your first upload the data in to array and then loop through the array. Once the data have been written over to new array (Y), you write it back to the range. It is much faster than looping through a range.

K64,
You are welcome!
Please mark this thread as solved!