0

Good morning,

currently I have this code to delete rows without due date (Column J) and amount paid=0 (Column H).

Sub delete_rows()

Range("A1").End(xlDown).Select
    
Sheets("AA").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-5]=0,"""",RC[-5])"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J500"), Type:=xlFillDefault
Range("J2").End(xlDown).Select
Range("K2").Select

Application.ScreenUpdating = False
With Sheet2
 For line = .Cells(.Rows.Count, "J").End(xlUp).Row To 2 Step -1
   If .Cells(line, "J") = "" Then
     .Rows(line).Delete
   End If
 Next linha
End With
Application.ScreenUpdating = True

ActiveCell.FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-4])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K500"), Type:=xlFillDefault
Range("K2").End(xlDown).Select
Range("J1").Select

Application.ScreenUpdating = False
With Sheet2
 For line = .Cells(.Rows.Count, "K").End(xlUp).Row To 2 Step -1
   If .Cells(line, "K") = "" Then
     .Rows(line).Delete
   End If
 Next line
End With
Application.ScreenUpdating = True
End sub()

I created a code with a defined number of lines...however it takes a long time for the code to run, because sometimes the number of lines is small and it always runs the 500 lines. What's the way to set the code so that it looks for the last filled row in column A, and then eliminate the rows where column H has values =0 and in column J no values?

Silva
  • 11
  • 6

1 Answers1

0

Please check: find last cell. Also have a look at: avoid select.

Afterwards, I think you should be able to understand the following code, which should get you the required result:

Sub test()

Application.ScreenUpdating = False

'declare your variables
Dim ws As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim i As Long, lastRow As Long

Set ws = Sheets("AA")

With ws

    'get last row in
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    
    'set ranges for loop
    Set Rng1 = Range(.Cells(2, "H"), .Cells(lastRow, "H"))
    Set Rng2 = Range(.Cells(2, "J"), .Cells(lastRow, "J"))
    
    'reverse loop
    For i = Rng1.Rows.Count To 1 Step -1

        'check conditions for cell in "H" and "J"
        If Rng1.Cells(i) = 0 And Rng2.Cells(i) = "" Then
        
            'defined ranges start at row 2, hence +1
            ws.Rows(i + 1).Delete
        
        End If

    Next i
    
End With

Application.ScreenUpdating = True

End Sub
ouroboros1
  • 9,113
  • 3
  • 7
  • 26
  • I tried some times, but that code is not running on my pc :/ – Silva May 04 '22 at 16:00
  • Could you specify where you run into an error with this code? If you enter the sub and go through it one step at a time (using `F8`), where does it fail and what does the error message say? (Or do you mean that it is not getting you the desired result?) – ouroboros1 May 04 '22 at 17:06
  • The code runs completly, but it does not execute anything. No error. – Silva May 05 '22 at 10:37
  • "It does not execute anything". I take it you mean that running the code doesn't delete any rows. Make sure your variables make sense. Are we in the correct sheet? Is `lastRow` being assigned the expected value? Etc. Use a couple of `Debug.Print` statements. E.g. underneath `lastRow = ...` use `Debug.Print (lastRow)`, underneath `For i ...` use `Debug.Print (Rng1.Cells(i).Parent.Name & "!" & Rng1.Cells(i).Address)`. Make sure Immediate Window is open (if not, open it with `Ctrl + G`). – ouroboros1 May 05 '22 at 11:11