Delete rows based on more than one condition.
 

I have tried the other answers without success because my grasp of vba is
just too weak.

I want to delete all rows if their respective cells in column "G" contains
either a value of less than 0.5 or "#VALUE!". My data range is from "A" to
"H" with variable number of variable rows and starting from row "3".


I appreciate a helping hand. Thank you.

Delete rows based on more than one condition.
 

On Sep 23, 9:05*am, thomas wrote:
I have tried the other answers without success because my grasp of vba is
just too weak.

I want to delete all rows if their respective cells in column "G" contains
either a value of less than 0.5 or "#VALUE!". My data range is from "A" to
"H" with variable number of variable rows and starting from row "3".

I appreciate a helping hand. Thank you.

Hello Thomas,

Give this a try. Untested. I'm not sure if the Error.Type will capture
the #Value, but if not someone here will definitely be able to tweak
this. It should select the range you want to delete, until you are
ready to delete.

Steven

Sub DelRows()
Dim Rng As Range
Dim DelRng As Range
Dim LRow As Long

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A3:H" & LRow)
Rng.AutoFilter Field:=Thing, Criteria1:="=0.5", _
Operator:=xlOr, Criteria2:="=" & Error.Type(3)

Set DelRng = Union(IIf(Rng Is Nothing,
SrcRng.Cells.SpecialCells(xlVisible), Rng), _
SrcRng.Cells.SpecialCells(xlVisible))
ActiveSheet.AutoFilterMode = False

If Not DelRng Is Nothing Then DelRng.EntireRow.Select
'change to Delete
Set DelRng = Nothing

End Sub

Delete rows based on more than one condition.
 

Steven,

When I tried your code, I got runtime error '424' Object required.


Thomas



" wrote:

On Sep 23, 9:05 am, thomas wrote:
I have tried the other answers without success because my grasp of vba is
just too weak.

I want to delete all rows if their respective cells in column "G" contains
either a value of less than 0.5 or "#VALUE!". My data range is from "A" to
"H" with variable number of variable rows and starting from row "3".

I appreciate a helping hand. Thank you.

Hello Thomas,

Give this a try. Untested. I'm not sure if the Error.Type will capture
the #Value, but if not someone here will definitely be able to tweak
this. It should select the range you want to delete, until you are
ready to delete.

Steven

Sub DelRows()
Dim Rng As Range
Dim DelRng As Range
Dim LRow As Long

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A3:H" & LRow)
Rng.AutoFilter Field:=Thing, Criteria1:="=0.5", _
Operator:=xlOr, Criteria2:="=" & Error.Type(3)

Set DelRng = Union(IIf(Rng Is Nothing,
SrcRng.Cells.SpecialCells(xlVisible), Rng), _
SrcRng.Cells.SpecialCells(xlVisible))
ActiveSheet.AutoFilterMode = False

If Not DelRng Is Nothing Then DelRng.EntireRow.Select
'change to Delete
Set DelRng = Nothing

End Sub

Delete rows based on more than one condition.
 

On Sep 23, 8:59*pm, thomas wrote:
Steven,

When I tried your code, I got runtime error '424' Object required.

Thomas

" wrote:
On Sep 23, 9:05 am, thomas wrote:
I have tried the other answers without success because my grasp of vba is
just too weak.

I want to delete all rows if their respective cells in column "G" contains
either a value of less than 0.5 or "#VALUE!". My data range is from "A" to
"H" with variable number of variable rows and starting from row "3".

I appreciate a helping hand. Thank you.

Hello Thomas,

Give this a try. Untested. I'm not sure if the Error.Type will capture
the #Value, but if not someone here will definitely be able to tweak
this. It should select the range you want to delete, until you are
ready to delete.

Steven

Sub DelRows()
Dim Rng As Range
Dim DelRng As Range
Dim LRow As Long

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A3:H" & LRow)
* * * * Rng.AutoFilter Field:=Thing, Criteria1:="=0.5", _
* * * * * * * * * * * * Operator:=xlOr, Criteria2:="=" & Error.Type(3)

Set DelRng = Union(IIf(Rng Is Nothing,
SrcRng.Cells.SpecialCells(xlVisible), Rng), _
* * * * * * SrcRng.Cells.SpecialCells(xlVisible))
* * * * * * ActiveSheet.AutoFilterMode = False

* * * * * * If Not DelRng Is Nothing Then DelRng.EntireRow.Select
'change to Delete
* * * * * * Set DelRng = Nothing

End Sub

Revised - Sorry, I rushed and didn't change a couple of variables I
had used in the other code.

S

Sub DelRows()
Dim Rng As Range
Dim DelRng As Range
Dim LRow As Long

LRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
LRow = 20
Set Rng = Range("A3:H" & LRow)
Rng.AutoFilter Field:=5, Criteria1:="=0.5", _
Operator:=xlOr, Criteria2:="=#VALUE!"

Set DelRng = Union(IIf(DelRng Is Nothing,
Rng.Cells.SpecialCells(xlVisible), DelRng), _

Rng.Cells.SpecialCells(xlVisible))
ActiveSheet.AutoFilterMode = False

If Not DelRng Is Nothing Then DelRng.EntireRow.Select
'change to delete
Set DelRng = Nothing

End Sub