Delete row on cell value RANGE

[selkov]

I would like to delete all rows on a worksheet where cells in column c are either in the range of "0-70" and "85-125".

I have done similar with a specific value but never a range.
can this be done?
Any examples?

 

[PCL]

Try next code

Sub DeleteRows()
Dim DelRg  As Range
Dim I  As Long, LR  As Long
Const WkCol As String = "C"
Const FR As Integer = 2
    LR = Cells(Rows.Count, WkCol).End(3).Row
    Set DelRg = Cells(LR + 1, WkCol)
    For I = FR To LR
        If (((0 < Cells(I, WkCol)) And (Cells(I, WkCol) < 70))) Then Set DelRg = Union(DelRg, Cells(I, WkCol))
        If (((85 < Cells(I, WkCol)) And (Cells(I, WkCol) < 125))) Then Set DelRg = Union(DelRg, Cells(I, WkCol))
    Next
    DelRg.EntireRow.Delete
'
End Sub

 

[selkov]

This deleted everything. thought maybe it was the statements 0< / 85< that should have been 0>. / 85> . But that deleted everything too.

 

[PCL]

We have to be clear about the limits, it is: 75 and not .75 neither /75 etc ...

 

[selkov]

Yes that is right the range of "0-70" and "85-125"

 

[PCL]

See here a data sample: Red values are deleted ...!
Of course data in column "C"

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]49[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]95[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]28[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]149[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]148[/TD]
[/TR]
</tbody>[/TABLE]

 

[selkov]

Sorry, that still did not work It deletes everything. But I got it to work like this: [just tweaked the #'s in the range to work]

LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = LR + 1 To 1 Step -1
ra = Range("C" & r)
If ra < 17 Then Rows(r).EntireRow.Delete
If ra > 19 Then Rows(r).EntireRow.Delete
Next

 

[PCL]

You are good and I am not.
There is a BIG, BIG error, when deleting rows we must always start by the BOTTOM

Sub DeleteRows()
Dim DelRg  As Range
Dim I  As Long, LR  As Long
Const WkCol As String = "C"
Const FR As Integer = 2
    LR = Cells(Rows.Count, WkCol).End(3).Row
    Set DelRg = Cells(LR + 1, WkCol)
    For I = LR to FR step -1
        If (((0 < Cells(I, WkCol)) And (Cells(I, WkCol) < 70))) Then Set DelRg = Union(DelRg, Cells(I, WkCol))
        If (((85 < Cells(I, WkCol)) And (Cells(I, WkCol) < 125))) Then Set DelRg = Union(DelRg, Cells(I, WkCol))
    Next
    DelRg.EntireRow.Delete
'
End Sub

I apologize