Remove line if x, add line if y

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
Good morning guys,

I would like to delete the entire row if my column M cells are equal to 0,000, starting on row 2.
I would also like to add a new line, immediately under the one where the condition is met, if my column K cells are greater than the matching column L cells.

My code is as follows:
VBA Code:
Sub play()

Dim livro1 As Workbook
Dim folha1 As Worksheet
Dim folha2 As Worksheet
Dim ultimalinha1 As Long

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

ultimalinha1 = folha1.Cells(Rows.Count, "A").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
    
Application.CutCopyMode = False

folha2.Activate

folha2.Range("A2").Select
    
End Sub

Any help is greatly appreciated.

Thanks!
 
Upping this for my side of the world.

Thanks
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Working Code:
If anyone has a suggestion on how to make it look more appealing, please feel free to share.

VBA Code:
Sub play()

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

Thanks!
 
Upvote 0
Solution

Forum statistics

Threads
1,215,103
Messages
6,123,112
Members
449,096
Latest member
provoking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top