Delete Rows with a +or- Tolerance

[Guzzlr]

Hello All,
I'm trying to delete all rows in column G that has a value of greater than 135%, or less than 40%
So if G2 has a value of 140%, than that row would be deleted. Or, if G2 had a value of less than 40%, then that row would also be deleted, and so on to the bottom of the data set.
The code I'm trying to use is:

For Each sht In ActiveWorkbook.Worksheets
If sht.Visible And (sht.Name) = "Template" Then
    For r = sht.Cells(sht.Rows.Count, "G").End(xlUp).Row To 4 Step -1
        If (sht.Cells(r, "G").Value) > "135%" or (sht.cells(r,"G:).value < "40%" Then sht.Rows(r).Delete
    Next r
    End If
Next sht

Also, I'm using r as Dim r as Range.

Thank you for your help


EDIT:
instead of a percent, it may be easer to find a value greater than .135 instead of 135% or a value less than .40 instead of 40%.
Also, I noticed that I originally said 140%, but that is wrong and should be 135%.
Thanks for the help

 

[Guzzlr]

I found a few syntax errors, but still not getting it to work.
Thanks for the help

For Each sht In ActiveWorkbook.Worksheets
If sht.Visible And (sht.Name) = "Template" Then
    For r = sht.Cells(sht.Rows.Count, "G").End(xlUp).Row To 4 Step -1
        If (sht.Cells(r, "G").Value) > "1.35" Or (sht.Cells(r, "G").Value) < ".40" Then sht.Rows(r).Delete
    Next r
    End If
Next sht

 

[JGordon11]

Change "1.35" to 1.35 and ".40" to .4

 

[Guzzlr]

Change "1.35" to 1.35 and ".40" to .4

Super...worked well, thanks for the help

 

[JGordon11]

Glad it worked.

Note that your first for..next loop is unnecessary. The code below will do the same thing without looping through all the sheets in the workbook.

Sub test()
    Dim r As Long, rg
    With Sheets("Template")
        If .Visible Then
            For r = .Cells(.Rows.Count, "G").End(xlUp).Row To 4 Step -1
                rg = .Cells(r, "G")
                If rg > 1.35 Or rg < 0.4 Then .Rows(r).Delete
            Next r
        End If
    End With
End Sub

 

[Guzzlr]

Glad it worked.

Note that your first for..next loop is unnecessary. The code below will do the same thing without looping through all the sheets in the workbook.

Sub test()
    Dim r As Long, rg
    With Sheets("Template")
        If .Visible Then
            For r = .Cells(.Rows.Count, "G").End(xlUp).Row To 4 Step -1
                rg = .Cells(r, "G")
                If rg > 1.35 Or rg < 0.4 Then .Rows(r).Delete
            Next r
        End If
    End With
End Sub

So this works, what I'm not understanding, is you have the Dimension as: Dim r As Long, rg.
However, when I write it as:
Dim r As Long
Dim rg As Long
It does not work...I'm not understanding why it does not work with two Dim's instead of combining the rg with the r?

 

[Joe4]

Dim r As Long
Dim rg As Long
It does not work...I'm not understanding why it does not work with two Dim's instead of combining the rg with the r?

The equivalent to what he wrote would actually be:

Dim r as Long
Dim rg

When you declare a value without a type, it is defaulted to "variant", which accepts everything.

Note that "Long" does NOT accept decimals, only whole numbers.
So, if you wanted to declare rg, you should do something like:

Dim rg as Double

Also, another common mistake people make is that they think that something like this:

Dim a, b as Long

declare both a and b as Long.
It does not.
Once again, "a" is variant.

Each variable needs to be declared explicitly if you don't want it to be variant, like this:

Dim a as Long, b as Long

or this:

Dim a as Long
Dim b as Long

 

[Guzzlr]

OK, thanks for explaining.
I'm using:
Dim r As Long
Dim rg As Double
This is working,
Thanks

The equivalent to what he wrote would actually be:

Dim r as Long
Dim rg

When you declare a value without a type, it is defaulted to "variant", which accepts everything.

Note that "Long" does NOT accept decimals, only whole numbers.
So, if you wanted to declare rg, you should do something like:

Dim rg as Double

Also, another common mistake people make is that they think that something like this:

Dim a, b as Long

declare both a and b as Long.
It does not.
Once again, "a" is variant.

Each variable needs to be declared explicitly if you don't want it to be variant, like this:

Dim a as Long, b as Long

or this:

Dim a as Long
Dim b as Long