Loop eval cell and actions not capturing last row

[alect71]

I have written vba for a macro which loops through a data extract, looks if a row contains a value in a certain column and if so, then performs some data copy/move functions. This is to loop until the end of the rows of data in the file.

Everything works well except the last row of data if it contains data in the requisite column, doesn't trigger the actions. It's fixed if I re-run the macro. But ideally it would complete property in one run. Given I am setting finalrow at the beginning and then inserting rows, I am guessing this is causing the last row to be missed. But I added an incremental +1 to finalrow within the loop which seemed to work for all rows except last. Not sure why. Any help appreciated (i've removed code not critical to the issue):

Dim finalrow As Integer 'final row of data available
Dim ws As Worksheet ' worksheet

Set ws = ThisWorkbook.Worksheets("Axis_Inv_Data")
ws.Activate
finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To finalrow
    '1 find row with km
    If IsEmpty(ws.Cells(i, 9)) = False Then

            
    '2 insert row after for kms
            Rows(i + 1).Insert
            
    '3 move km data to new row
            
    '4 copy shift data to new km row
            
     ' add one more row to total to ensure we don't miss last row
     finalrow = finalrow + 1
     
    End If
    
Next i


End Sub

[alansidman]

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

[alect71]

ummm this is my first and only post on here. Not sure where you think I am cross-posting?

[jolivanes]

Hi Alan.
I don't see the hyperlink to the other forum, and I don't know which forum it is/was.
I assume that it is OK to get going again if the reference has been deleted.

To the OP
How does it work for you if you start from the bottom?

For i = lr To 2 Step -1

[alect71]

To the OP
How does it work for you if you start from the bottom?

For i = lr To 2 Step -1

Seems to work like a charm! Thanks!

[jolivanes]

Just for future reference, when deleting and/or inserting it is suggested to start from the bottom up or from the right to the left in case of columns.
Did you not post the same question on another forum?
If you did not, Alan might have posted by accident in this Post.

[AliGW]

The canned message in post #2 is the wrong message. It should have been the one about adding code tags - Alan has added these to the opening post.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[alect71]

Thanks - makes sense and will do better

[alect71]

Just for future reference, when deleting and/or inserting it is suggested to start from the bottom up or from the right to the left in case of columns.
Did you not post the same question on another forum?
If you did not, Alan might have posted by accident in this Post.

a follow up question - i am using the same (structured) macro code for another purpose where the action is only deleting an entire row if a column value meets criteria. Except it seems to run and only delete the bottom row and then stops. I think it's because the finalrow and looping backwards is getting thrown off as the final row is being deleted? Suggestions?

[alansidman]

Thanks Ali. Really need to pay closer attention to which canned response I am tapping.

Alan

[jolivanes]

AliGW/Alan
OK, now it makes sense to me. Thanks

[jolivanes]

No, it does not. There is something else not right but we don't have your workbook nor your code to check.
If you say "meets criteria", it could be several possibilities. Spelling, trailing spaces, cells that appear empty but have a non printable character in it and so on.
The best thing to do is attach a copy of your workbook, sanitized and sized to a reasonable size and a before and after inside the workbook with an explanation on how you arrived at the end result.

[alect71]

Below is the code. When I run it it deleted the first instance from the bottom but then appears to stop (ie doesn't delete the other instances which should

Sub Clean_Up_Shifts()

'1. find row with non payable shifts (transit between clients)
'2. delete rows


Dim i As Integer 'row counter
Dim finalrow As Integer 'final row of data available
Dim ws As Worksheet ' worksheet

Set ws = ThisWorkbook.Worksheets("Axis_Shift_Data")
ws.Activate
finalrow = Cells(Rows.Count, 1).End(xlUp).Row


For i = finalrow To 2 Step -1
    '1 find row with non payable shifts (inter client travel)
    
    If InStr(ws.Cells(i, 15), "Travel") > 0 Then

            
    '2 delete row
            Rows(i + 1).EntireRow.Delete
            
     
    End If
    
Next i


End Sub

a sample worksheet is attached

[jolivanes]

Your naming of the Worksheet in the code is different from your actual Worksheet Name.
Why do you have the + 1 in the code? That will delete the row below where it finds "Travel"

Sub Clean_Up_Shifts()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Axis Shift Data")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
    With ws
        For i = lr To 2 Step -1
            If InStr(ws.Cells(i, 15), "Travel") > 0 Then .Cells(i, 15).EntireRow.Delete
        Next i
    End With
Application.ScreenUpdating = True
End Sub

In your case, you could replace this

If InStr(ws.Cells(i, 15), "Travel") > 0 Then .Cells(i, 15).EntireRow.Delete

with this

If Cells(i, 15).Value Like "*Travel*" Then .Cells(i, 15).EntireRow.Delete

if you want to.

[alect71]

Your naming of the Worksheet in the code is different from your actual Worksheet Name.
Why do you have the + 1 in the code? That will delete the row below where it finds "Travel"

Good question - no idea. That fixed the problem! thanks.

In your case, you could replace this

If InStr(ws.Cells(i, 15), "Travel") > 0 Then .Cells(i, 15).EntireRow.Delete

with this

If Cells(i, 15).Value Like "*Travel*" Then .Cells(i, 15).EntireRow.Delete

if you want to.

Will try that if i need to.

[jolivanes]

Good that all is working.
In he future, don't quote whole posts. Refer to Post # if necessary.
Good Luck

[Vraag en antwoord]

Use the autofilter. Is a lot faster

Sub VenA()
  With Sheets("Axis Shift Data").Cells(1).CurrentRegion
   .AutoFilter 15, "*Travel*"
   .Offset(1).EntireRow.Delete
   .AutoFilter
  End With
End Sub

[jolivanes]

@ Vraag en antwoord.
It certainly is faster with autofilter but with a relative small range of data I don't think it'll make much of a difference.
Have you compared it?

[jolivanes]

Just for the heck of it, tried it with different amounts of data.
100 Rows of data: Loop = 0.19 sec, AutoFilter = 0.08 sec
500 Rows of data: Loop = 0.74 sec, AutoFilter = 0.11 sec
5000 Rows of data: Loop = 5.27 sec, AutoFilter = 0.50 sec
When looking at this, no use going to higher numbers. Unless you want the numbers.
I included Application.ScreenUpdating = False/True in VeA's code
So yes, stick to AutoFilter.

[jolivanes]

If it is speed you need, this is twice as fast as AutoFilter with 10,000 Rows of data.

Sub With_Array()
Dim arr1, arr2, i As Long, j As Long, x As Long, y As Long, k As Long, m As Long
arr1 = Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Value
x = 0
For i = LBound(arr1) To UBound(arr1)
    If arr1(i, 15) Like "*Travel*" Then
    x = x + 1
        For j = 1 To 16
            arr1(i, j) = ""
        Next j
    End If
Next i
ReDim arr2(1 To UBound(arr1) - x, 1 To 16)
y = 0
    For k = 1 To UBound(arr1)
        If arr1(k, 1) <> "" Then
            y = y + 1
            For m = 1 To 16
                arr2(y, m) = arr1(k, m)
            Next m
        End If
    Next k
ActiveSheet.UsedRange.ClearContents
Cells(1, 1).Resize(UBound(arr2), 16).Value = arr2
End Sub