On Sep 23, 8:59*pm, thomas <tho...@discussions.microsoft.com> wrote:
> Steven,
>
> When I tried your code, I got runtime error '424' Object required.
>
> Thomas
>
> "sbit...@gmail.com" wrote:
> > On Sep 23, 9:05 am, thomas <tho...@discussions.microsoft.com> 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
|
Similar Threads
