Deleting a row by a column value
 

This code was in another topic, and I tried utilizing it to look i
Column U for anything that's not D6 and delete that row. Here's what
got but the debug keeps pointing to the ActiveSheet.ShowAllData part o
the code. What am I doing wrong in this code?

Dim rng As Range, rng1 As Range
Range("U1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<D6", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("U1").CurrentRegion.AutoFilter


Sub DeleteRows()
Dim rng As Range, rng1 As Range
Range("C1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<26", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("C1").CurrentRegion.AutoFilter
End Sub


--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=26735

Deleting a row by a column value
 

DKY,

You don't need it, since you are turning off your autofilter. But you had
one other possible problem with the code: the fcombination of setting the
field to a constant (3) and using the currentregion, which may change over
time. See below for a more robust working version.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim rng As Range, rng1 As Range
Range("U:U").AutoFilter _
Field:=1, Criteria1:="<D6"
On Error Resume Next
Set rng1 = Range("U2:U65536").SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
Range("U1").CurrentRegion.AutoFilter
End Sub



"DKY" wrote in message
...

This code was in another topic, and I tried utilizing it to look in
Column U for anything that's not D6 and delete that row. Here's what I
got but the debug keeps pointing to the ActiveSheet.ShowAllData part of
the code. What am I doing wrong in this code?

Dim rng As Range, rng1 As Range
Range("U1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<D6", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("U1").CurrentRegion.AutoFilter


Sub DeleteRows()
Dim rng As Range, rng1 As Range
Range("C1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<26", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("C1").CurrentRegion.AutoFilter
End Sub



--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=267358

Deleting a row by a column value
 

Read one more reply at your other post.

DKY wrote:

This code was in another topic, and I tried utilizing it to look in
Column U for anything that's not D6 and delete that row. Here's what I
got but the debug keeps pointing to the ActiveSheet.ShowAllData part of
the code. What am I doing wrong in this code?

Dim rng As Range, rng1 As Range
Range("U1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<D6", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("U1").CurrentRegion.AutoFilter


Sub DeleteRows()
Dim rng As Range, rng1 As Range
Range("C1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<26", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
ActiveSheet.ShowAllData
Range("C1").CurrentRegion.AutoFilter
End Sub


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=267358

--

Dave Peterson