CODEWITHSUNDEEP
excelvbarows
Niloo
Niloo

Reputation: 35

Remove rows that are not between two other cells

I am trying to remove entire rows in which one of their cells is not between two values (the values are also written in two different cells).

I have 31 rows and 60 columns (all filled with numbers). I need to compare all the values in column 58 (31 numbers) to two cells (for example cells BK1 and BL1) and if these values are not between those two cells values, remove the entire row that contains this value.

I wrote the code below.

Sub Deleterow()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim c As Range

Set shSource = wb.Sheets("Sheet1")

    For i = 1 To 31
        If (Sheet1.Range("BK1").Value2 = "") And (Sheet1.Range("BL1") _
           .Value2 = "") Then GoTo Line1
        End If
Line1:
        If (Sheet1.Range(i, 59).Value2 < Sheet1.Range("BK1").Value2 _
            Or Sheet1.Range(i, 59).Value2 > Sheet1.Range("BL1").Value2) Then

            Delete.Rows (i)
        End If
    Next

End Sub

Upvotes: 0

Views: 120

Answers (1)

SNicolaou
SNicolaou

Reputation: 550

Test the code below. Also, do you really want to delete the first line? i.e. the line that holds BK1, BL1 ?

Option Explicit

Public Sub sDeleteRow()

  Dim ws As Worksheet, r As Range
  Dim lowerValue As Variant, upperValue As Variant, testValue As Variant
  Dim i As Long

  Set ws = ThisWorkbook.Sheets("Sheet1")

  If ws.Range("BK1").Value = "" Or ws.Range("BL1").Value = "" Then
    MsgBox "Can't run macro as BK1 or BL1 is blank"
    Exit Sub
  End If

  lowerValue = ws.Range("BK1").Value
  upperValue = ws.Range("BL1").Value

  If IsNumeric(lowerValue) And IsNumeric(upperValue) Then
    For i = 31 To 2 Step -1
      Set r = ws.Cells(i, 59)
      testValue = r.Value
      If IsNumeric(testValue) Then
        If (testValue < lowerValue And testValue > upperValue) Then
          r.EntireRow.Delete
        End If
      End If
    Next i
  End If
End Sub

Upvotes: 1

Related Questions