Define and call a constant

[powerdink]

I'm trying to declare a constant that I can call in an if statement. The excerpt of code below is supposed to search all cells in column E and find where the value is equal to or less than 14. If it is, then the script will clear the contents of the entire row.

Dim u1 As Range
Const conAGEy As Integer = 14
 
Set u1 = ThisWorkbook.Worksheets("Sheet1").Range("B2").CurrentRegion
    With u1.Offset(1, 0).Resize(u1.Rows.Count, u1.Columns.Count)
        Last = Cells(Rows.Count, "A").End(xlUp).Row
        For i = Last To 2 Step -1
            If (Cells(i, "E").Value) <= conAGEy Then         'Constants are not working here
                Cells(i, "A").EntireRow.ClearContents
            End If
        Next i
    End With

Any help would be appreciated. Thanks all.

 

[RoryA]

That code looks fine to me. What is your problem with it?

 

[powerdink]

It doesn't actually go through and clear the rows where Column "E"'s value is <= the value of the constant. I don't get an error message either. It just runs like it completed, except the data is still there.

 

[RoryA]

Are you sure the values in your sheet are numbers and not text? Does it work if you simply use 14 directly in place of the constant?

 

[JackDanIce]

Do you have any gaps in your data, say row 3 is empty or anything below? Otherwise I can't see what's wrong either.. maybe change the clearcontents line to delete?

Cells(i, "A").EntireRow.Delete

Since you're looping backwards anyway...

 

[powerdink]

Thanks guys, I tried inserting 14 instead of the constant and it still doesn't update. So I added-

.numberformat = "0"

-earlier to format the column to numbers just in case it was text and that didn't work either.

Lastly, I tried adding delete instead of clear rows but none of it seems to work. Any ideas?

 

[RoryA]

Changing a number format will not in any way alter the contents of cells. Are your numbers left-aligned by default? If so they are text. Copying a blank cell then select the numbers and paste special - add

 

[powerdink]

Got this to work by making some modifications to the code. Turns out that without using the .cells to show the object in the with statement, the loop will execute through the entire sheet. I also took out the column names or "E" and replaced it with the column number.

    Set u4 = ThisWorkbook.Worksheets("Sheet1").Range("G2").CurrentRegion
    With u4.Offset(1, 0).Resize(u4.Rows.Count - 1, u4.Columns.Count)
        Last = .Cells(.Rows.Count, 5).Row
        For i = Last To 2 Step -1
            If (.Cells(i, 5).Value) <= conAGE1 Then
                .Rows(i).EntireRow.ClearContents
            End If
        Next i
    End With