How to skip empty cells within a UsedRange in a loop

Question

A few days ago the structure of the data I was working with did not have empty cells in column D I am copying rows based on Qty column and Pasting it the "cell value times" below then continuing to the next row. But when I got a dirty set of data today, my head started spinning. The code below works well so long as Column D Does not contain empty cells. If it does, the code fails instantly. I am not sure how to correctly use the UsedRange property to skip to the next cell or even fill them up with zero so that the code can continue.

My question here is, how do you skip empty cells within a UsedRange and go to the next non empty cell? Any advised is highly welcome. Thanks

Please see below code and images:

Dim xRow As Long
Dim inNum As Variant
Dim rowIndex As Integer
Dim lastRow As Long
Dim lastCol As Long
xRow = 1


    Do While (Cells(xRow, "D") <> "")
    
    inNum = Cells(xRow, "D")
       If ((inNum > 1) And IsNumeric(inNum)) Then
       Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
       Range(Cells(xRow + 1, "A"), Cells(xRow + inNum - 1, "D")).Select
       Selection.Insert Shift:=xlDown
       xRow = xRow + inNum - 1
    End If
    
    If ((inNum < -1) And IsNumeric(inNum)) Then
       Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
       Range(Cells(xRow + 1, "A"), Cells(xRow + Abs(inNum) - 1, "D")).Select
       Selection.Insert Shift:=xlDown
       xRow = xRow + Abs(inNum) - 1
    End If

       xRow = xRow + 1
   Loop

Current data:

Expected:

Possibly better to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first and then loop to it, instead of your current `Do While` loop approach. Actually I'd think you'd want to loop from the bottom up too. — BigBen, Apr 15 '21 at 18:00
@BigBen I find use `lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row` and `lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column` Are you suggesting a `For Loop`? — WKI, Apr 15 '21 at 18:05
Definitely a `For` loop and from the bottom up too using `Step -1`. — BigBen, Apr 15 '21 at 18:05
Why are you doubling those three entries (records), yet not halving their quantity? What should happen if the quantity is 3? — VBasic2008, Apr 15 '21 at 18:17
@VBasic2008 the records only doubles or the `Qty` field is 2, if `Qty` is 3, it will appear 3 times and so on. — WKI, Apr 15 '21 at 18:25

How to skip empty cells within a UsedRange in a loop

0 Answers0