Excel VBA isn't Validating integer values corr

Question

Basically I am trying to go through cells that have a value of less than 50 in a column(R:R)

Here is the code:

Sub Macro1()
'
' Macro1 Macro
'
'
    Sheets("Pyxis Inventory 6North1 and 6No").Select
    Cells(1).EntireRow.Copy (Sheets("Sheet1").Range("A1").EntireRow)
    Cells(1).EntireRow.Copy (Sheets("Sheet2").Range("A1").EntireRow)

    Dim val1, val2 As String
    Dim i, j, x As Integer
    Dim colCount As Integer
    Dim daysUnused As Boolean
    Dim daysVal As Integer
    colCount = Sheets("Pyxis Inventory 6North1 and 6No").Range("A1").CurrentRegion.Rows.Count

    daysUnused = IsEmpty(Sheets("Settings").Range("B2"))
    If daysUnused = True Then
        'do nothing yet

    ElseIf daysUnused = False Then
        daysVal = Sheets("Settings").Range("B2").Value
        For x = 2 To colCount
            If Cells(x, 18).Value <= daysVal Then
                Cells(x, 18).EntireRow.Copy (Sheets("Sheet4").Range("A1").Offset(x, 0))
                Cells(x, 18).EntireRow.Delete
            End If
         Next x
    End If
End Sub

My problem is that it doesn't delete rows with numbers less than 50.

I have formatted the cells in the column 18 to be of number only as well.

What i want to do is basically sort, but instead of keeping the values I want to delete anything that is less than 50 or whatever the user enters for daysVal variable.

Thank you

Answer 1

To start with it, it is best practise when deleting rows to start at the bottom and work up. e.g. For x = colCount to 2 step -1.

The If daysUnused = True and ElseIf daysUnused = False Then are over-evaluating a simple boolean. The latter could be shortened to Else. By strict definition, if you are not going to do anything when True, a Not should be more than enough.

daysUnused = IsEmpty(Sheets("Settings").Range("B2"))
If Not daysUnused Then
    daysVal = Sheets("Settings").Range("B2").Value
    For x = colCount to 2 step -1
        If Cells(x, 18).Value <= daysVal Then
            Cells(x, 18).EntireRow.Copy Sheets("Sheet4").Range("A1").Offset(x, 0)
            Cells(x, 18).EntireRow.Delete
        End If
     Next x
End If

It sounds like you fell into the row-skipping trap that makes bottom-to-top (and right-to-left when deleting columns) a canonical practise. Hopefully the above will help you tighten up your code.