Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting every nth through every nth row in a column | Excel Discussion (Misc queries) | |||
Deleting nth row or column | Excel Discussion (Misc queries) | |||
Deleting a column | Excel Discussion (Misc queries) | |||
Deleting a Column | Excel Programming | |||
Deleting a row with 0 in column A | Excel Programming |