CODEWITHSUNDEEP
vbaexcel
Dominic Landert
Dominic Landert

Reputation: 21

Macro skips lines depending on insert file

I have a large macro which takes price book information from a vendors file, formats it and then creates a .txt file which then can be uploaded into the system. For this I prompt the user several times to select for example the column which contains information about the part number or the list price. So far so good. When it comes to the following code, the macro just skips it:

'delete rows with no price  
p = 0  
For i = 3 To LastRow  
    If p > LastRow Then  
        GoTo Ziel4  
    ElseIf IsEmpty(wsImport.Cells(i, 8)) = True Then  
        Rows(i).Delete  
        i = i - 1  
    End If  

    p = p + 1  
Next i

However, if I execute the code with a break point right before the loop and execute it, it works just fine. When I execute the whole script with another vendors file, it just skips 3/4 of the script and produces an empty .txt file. I already tried it with Application.ScreenUpdating = False to improve performance but it still keeps skipping lines.

Upvotes: 0

Views: 83

Answers (2)

EarlyBird2
EarlyBird2

Reputation: 306

Make sure "LastRow" has a value prior to looping as stated by "DisplayName" above, and I would assume you want to start with the last row and then move up again when searching for empty rows, otherwise it would not make much sense, right? Question is, what is p used for? Why do you increase it by 1/iteration and abort the loop if it becomes > LastRow?

Try:

For i = LastRow To 3 Step -1  'Start at the end and move up, should save you the "p"

    If IsEmpty(wsImport.Cells(i, 8)) = True Then  
        Rows(i).Delete  
    End If  

Next i

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

not so sure about your code exact goal, but since you "prompt the user several times" and everything works when you "execute the code with a break point right before the loop", I suspect you're jumping between worksheets and hence you're prone to miss the proper worksheet

so change:

Rows(i).Delete

to:

wsImport.Rows(i).Delete

Upvotes: 1

Related Questions