Delete entire rows with negative numbers in Amount Column - VBA

[robw824]

Hi. My fairly large spreadsheet has 23 columns and can have any number of rows (current one 969). I want to use VBA to delete the entire row for every row that contains a negative number in column K ($ amount)

 

[RobP]

Hi, would this help you ?

It checks for the last row of data in Column A (eg. Col. 1 or "A" MUST have data in each cell, no blanks, else there is room for error (eg it could miss the last few rows..)

Sub delete_row()

Dim datalastrow, x As Long

datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in colA

For x = datalastrow To 1 Step -1

    If ActiveSheet.Range(Cells(x, 11), Cells(x, 11)) < 0 Then  'K equates to Col 11 here
      
        Range(Cells(x, 11), Cells(x, 11)).EntireRow.Delete

    End If

Next x

End Sub

 

[robw824]

Hi.
There are 5 different data types, RV, DA, DZ, RR, RT (in column L) I only want to delete the rows with negative numbers with the type RV.
I had the sheet filtered by type RV. but when running your script it deleted all negative rows in all types. I thought it would only delete the rows displayed.
Is there a way to change it so that it only deletes the negative rows with neg amounts in column K and "RV" in Column L?

Account	Company	Line	Document	Assignment	Date	Due	Terms	​ ​ ​ ​ Arrears	C/O	Amount	Type
38308​	1130​	1​	910026792​	910026792​	12/19/2023	12/19/2023	A003	20	0​	(35,789.60)	RV
38308​	1130​	1​	98708995​	98708995​	3/28/2022	3/28/2022	A003	651	0​	-2381.40	RR
38308​	1130​	1​	90018113​	90018113​	11/8/2023	11/8/2023	A003	61	0​	(32,523.40)	RV
38308​	1130​	1​	910057714​	910057714​	1/4/2024	1/4/2024	A003	4	0​	(17,225.65)	RV
38308​	1130​	1​	99379853​	99379853​	1/17/2023	1/17/2023	A003	356	0​	(12,962.29)	RV
38308​	1130​	3​	1400565408​	240​	12/1/2022	1/1/2023	0​	372	0​	-26.24	DZ

 

[RobP]

Hi, just add the AND portion to the code as such :

Sub delete_row()

Dim datalastrow, x As Long

datalastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' find the last row of data in colA

For x = datalastrow To 1 Step -1

    If ActiveSheet.Range(Cells(x, 11), Cells(x, 11)) < 0 AND ActiveSheet.Range(Cells(x, 12), Cells(x, 12)) = "RV" Then  'K equates to Col 11 here
      
        Range(Cells(x, 11), Cells(x, 11)).EntireRow.Delete

    End If

Next x

End Sub

 

[Peter_SSs]

Rather than cycling through about 1,000 rows individually, what about deleting all the relevant ones at once?

Sub Del_rows_v1()
  Application.ScreenUpdating = False
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
  With Range("A1").CurrentRegion
    .AutoFilter Field:=12, Criteria1:="RV"
    .AutoFilter Field:=11, Criteria1:="<0"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=11
    .AutoFilter Field:=12
  End With
  Application.ScreenUpdating = True
End Sub

 

[RobP]

Peter,

that definately works .. (I had no doubt ) .. but I guess I'm struggling to understand actually this terminology a little, and what its doing after you've filtered your data. Can you elaborate a little as this method seems far more efficient ?

 If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete

 

[Peter_SSs]

If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete

After the filter you will have the headings and probably some other rows of data. If there happened to be no rows of data visible after the filters are applied and you omitted the red part above, then the code would delete all the data below the headings even though it is in hidden rows. So the red part is checking to make sure there is at least one row of visible data to delete.

If you want to check, remove the red part of the code and test on some data that has no negative RV rows