VBA if cells between number values then delete row

Question

Have a large data set. there are formulas in cells AX:BA

Below is part of the code:

    'Formats cells in column
    Range("AX:BA").NumberFormat = "$#,##0.00"

    'sorts column BA descending
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort key1:=Range("Ba1", Range("ba1").End(xlDown)), _
    Order1:=xlDescending, Header:=xlYes

    'deletes any row in AX that is #N/A
    lr = Cells(Rows.Count, "AX").End(xlUp).Row
    For i = lr To 2 Step -1
    If Cells(i, "AX").Text = "#N/A" Then Rows(i).EntireRow.Delete
    Next i

What I'd like to do is replicate the row deletion, but rather than looking for #N/A, it deletes a row where BA is between -$0.01 and $0.01. Alternatively it could delete where absolute value is less than or equal to 1. I need to replace Text = "#N/A" below:

  lr = Cells(Rows.Count, "BA").End(xlUp).Row
    For i = lr To 2 Step -1
    If Cells(i, "BA").Text = "#N/A" Then Rows(i).EntireRow.Delete
    Next i

Not even sure where to start, since I've essentially googled how to do 95% of the current code

Ryan H · Accepted Answer · 2025-08-25 15:58:29Z

2

To modify your VBA code to delete rows where the value in column BA is between -$0.01 and $0.01 (or equivalently, where the absolute value is less than or equal to 0.01), you need to replace the condition Cells(i, "AX").Text = "#N/A" with a check for the absolute value of the cell in column BA. Since column BA is formatted as currency ($#,##0.00), you can use the ABS function to evaluate the absolute value of the cell’s numeric value. Here’s the updated code snippet to replace the row deletion logic:

'Formats cells in column
Range("AX:BA").NumberFormat = "$#,##0.00"

'sorts column BA descending
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort key1:=Range("BA1", Range("BA1").End(xlDown)), _
    Order1:=xlDescending, Header:=xlYes

'deletes any row in AX that is #N/A
lr = Cells(Rows.Count, "AX").End(xlUp).Row
For i = lr To 2 Step -1
    If Cells(i, "AX").Text = "#N/A" Then Rows(i).EntireRow.Delete
Next i

'deletes any row in BA where absolute value is <= 0.01
lr = Cells(Rows.Count, "BA").End(xlUp).Row
For i = lr To 2 Step -1
    If IsNumeric(Cells(i, "BA").Value) And Abs(Cells(i, "BA").Value) <= 0.01 Then
        Rows(i).EntireRow.Delete
    End If
Next i

answered Aug 25 at 15:58

Ryan H

563 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stefan Brod Aug 25 at 16:17

Thank you, this works beautifully. Appreciate the explanation as well!

CDP1802 Aug 25 at 17:23

Note - If the value is non-numeric the Abs function will produce a Type mismatch error.

VBasic2008 · Accepted Answer · 2025-08-25 19:00:00Z

Delete Rows with Values Between...

Option Explicit

Sub DeleteRows()
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' If the code is in another workbook, e.g., in 'PERSONAL.xlsb',
    ' use the workbook's name, i.e.,
    'Dim wb As Workbook: Set wb = Workbooks("MyBook.xlsx") ' , or use
    'Dim wb As Workbook: Set wb = ActiveWorkbook
    ' making sure the correct workbook is active before running the code.
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    ' If you don't know its name or the code should work
    ' for multiple worksheets, use
    'Dim ws As Worksheet: Set ws = wb.ActiveSheet
    ' making sure the correct worksheet is active before running the code.
    
    ' Reference the range (this simplification assumes the table starts
    ' in cell 'A1' with contiguous data (and one row of headers)).
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim RowsCount As Long: RowsCount = rg.Rows.Count
    
    ' Usually you don't want to format or do anything to entire columns
    ' (except adjust the width). Therefore, clear the cells below the data
    ' to remove any existing values and formatting
    ' (this simplification assumes the table starts in row '1').
    rg.Resize(ws.Rows.Count - RowsCount).Offset(RowsCount).Clear
    
    ' Reference the sort (single-column) range.
    Dim srg As Range: Set srg = ws.Columns("BA")
    
    ' Sort descending by the sort range.
    rg.Sort Key1:=srg, Order1:=xlDescending, Header:=xlYes

    ' Return the values of the sort range in an array
    ' (to increase reading efficiency).
    Dim Data() As Variant: Data = srg.Value ' assumes at least two rows

    ' Declare additional variables.
    Dim Value As Variant, Row As Long
    
    ' Loop through the rows of the array and delete the 'table' rows
    ' where the value (from the sort column) is between the two given numbers.
    For Row = RowsCount To 2 Step -1 ' exclude header row
        Value = Data(Row, 1)
        Select Case VarType(Value)
            Case vbDouble, vbCurrency ' is a number (or Currency)
                ' Often one side is '... than or equal'
                ' and the other is '... than'. Adjust as required.
                If Value >= -0.01 And Value < 0.01 Then
                    ' Uncomment the following line to list the values
                    ' and addresses of the rows that were deleted in the
                    ' Immediate window (Ctrl+G) (it can show only 200 rows
                    ' and will slow down the code execution though).
                    'Debug.Print Value, rg.Rows(Row).Address(0, 0)
                    ' Delete 'table' row instead of entire row.
                    rg.Rows(Row).Delete Shift:=xlShiftUp
                End If
            'Case Else ' is not a number (or Currency); do nothing
        End Select
    Next Row
    ' Note that 'rg' now refers to the new 'table' range with fewer rows
    ' (if any deleted)!
            
    ' Apply number formatting to data cells (headers excluded) in given columns.
    With rg.Columns("AX:BA")
        .Resize(rg.Rows.Count - 1).Offset(1).NumberFormat = "$#,##0.00"
    End With
    
End Sub

Collectives™ on Stack Overflow

VBA if cells between number values then delete row

2 Answers 2

2 Comments

Delete Rows with Values Between...

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Delete Rows with Values Between...

Comments

Your Answer

Sign up or log in

Post as a guest

Related