My code only runs until the end if I double click my assigned macro button. Help Please

[jalrs]

Hello guys,

I suspect it is a double if clause related issue, but as stated on the title, I need to click twice on my macro assigned button to make it run until the end.
First click it deletes the rows according first if clause
Second click it adds rows according to second if clause.

Code as follows and I can share sample of data with you, if needed.

Sub play2()

Dim livro1 As Workbook
Dim folha1 As Worksheet
Dim folha2 As Worksheet
Dim ultimalinha1 As Long, ultimalinha2 As Long, ultimalinha3 As Long, i As Long

Set livro1 = ThisWorkbook
Set folha1 = livro1.Worksheets("ZPO1")
Set folha2 = livro1.Worksheets("Play")

ultimalinha1 = folha1.Cells(Rows.Count, "A").End(xlUp).Row
ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row

folha2.UsedRange.Offset(1).Cells.ClearContents

    With folha1
    
        .Range("A2:O" & ultimalinha1).Copy
        folha2.Cells(2, 1).PasteSpecial Paste:=xlPasteFormats
        folha2.Cells(2, 1).PasteSpecial Paste:=xlPasteValues
    
    End With
    
    For i = ultimalinha2 To 2 Step -1
    
        If folha2.Cells(i, "M") = "0" Then
            folha2.Rows(i).Delete
        
        End If
    
    Next i
    
    For i = ultimalinha3 To 2 Step -1
        
        If folha2.Cells(i, "L").Value > folha2.Cells(i, "M").Value Then
            folha2.Rows(i).Insert
        
        End If

    Next i
    
Application.CutCopyMode = False

folha2.Activate

folha2.Range("A2").Select

End Sub

Any help is greatly appreciated.

Thanks

 

[rollis13]

It happens because you are calculating the 2 last rows when the sheet "Play" is still populated from a previous session that has used a different range:

ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row

after that you then clear the usedrange with:

folha2.UsedRange.Offset(1).Cells.ClearContents

These 2 variables need to be calculated only after pasting your new data. So, move those 2 lines of code here:

'...
End With
ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row   '<- moved here
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row   '<- moved here
For i = ultimalinha2 To 2 Step -1
'...

 

[jalrs]

It happens because you are calculating the 2 last rows when the sheet "Play" is still populated from a previous session:

ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row

after that then you clear the range with:

folha2.UsedRange.Offset(1).Cells.ClearContents

These 2 variables need to be calculated only after pasting your new data. So, move those 2 lines of code here:

'...
End With
ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row   '<- moved here
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row   '<- moved here
For i = ultimalinha2 To 2 Step -1
'...

Hey rollis,

Thanks for your answer, I'll check that out in a few.

Thanks!

 

[jalrs]

It happens because you are calculating the 2 last rows when the sheet "Play" is still populated from a previous session that has used a different range:

ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row

after that you then clear the usedrange with:

folha2.UsedRange.Offset(1).Cells.ClearContents

These 2 variables need to be calculated only after pasting your new data. So, move those 2 lines of code here:

'...
End With
ultimalinha2 = folha2.Cells(Rows.Count, "M").End(xlUp).Row   '<- moved here
ultimalinha3 = folha2.Cells(Rows.Count, "L").End(xlUp).Row   '<- moved here
For i = ultimalinha2 To 2 Step -1
'...

Hey rollis,

It works, thanks. Additionally could you please tell me why my code is adding a row before and after the desired row? (first if) I want it to only add after and not before. Should be a quick fix. I just noticed this now

Thanks

 

[Alex Blakenburg]

Try changing your insert line to this:

            folha2.Rows(i + 1).Insert

 

[rollis13]

Here is where your macro inserts a blank row before the row in processing when the value of column L > M :

For i = ultimalinha3 To 2 Step -1
    If folha2.Cells(i, "L").Value > folha2.Cells(i, "M").Value Then
        folha2.Rows(i).Insert
    End If
Next i

 

[jalrs]

Try changing your insert line to this:

            folha2.Rows(i + 1).Insert

Hey Alex!

This works, thank you. I tried before

shift:= xlDown

but didn't work.

Thanks for the help!

Here is where your macro inserts a blank row before the row in processing when the value of column L > M :

For i = ultimalinha3 To 2 Step -1
    If folha2.Cells(i, "L").Value > folha2.Cells(i, "M").Value Then
        folha2.Rows(i).Insert
    End If
Next i

Hey rollis,

Thanks for your answer, I refered to it wrongly, should have been second if clause and not first if clause. However, Alex stepped in and made it work.

Thanks! Marked as solution

 

[rollis13]

Thanks for the positive feedback. Glad we were able to help.