It looks like there's a bug in excel.

If you delete all the rows (except the headers), then excel has a difficult time
showing all the data.

This kind of code usually works:

If activesheet.FilterMode Then
activesheet.ShowAllData
end if

But when I deleted all the rows in the table, it still blew up--In fact, I
couldn't get Data|filter|showall to remove that blue dropdown arrow. (Excel
still thought that the sheet was filtered.

But this code:

on error resume next
activesheet.showalldata
on error goto 0

Stopped the error from causing a runtime error.

But even better, the following line:
Range("U1").CurrentRegion.AutoFilter
toggled the autofilter off.

(The short answer is coming (promise).)

Delete that offending line and you'll be ok.

========
The real question is are you sure you're doing what you want in your code:

These two lines look like trouble to me:

Range("U1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="<D6", Operator:=xlAnd
Set rng = Intersect(ActiveSheet.AutoFilter.Range, Columns(3))

If you select U1 and hit ctrl-* (shift-8) what's the range that's selected.

That's the range that's filtered. And you're using the 3 column as your key.

If you got columns A:U (or more), then field 3 is column C.

And that's the column that you're checking to see if it's different from "D6".

This may work better for just checking one column (U):

Option Explicit
Sub testme01()
Dim rng As Range, rng1 As Range

With ActiveSheet
.AutoFilterMode = False

.Range("U1").EntireColumn.AutoFilter _
Field:=1, Criteria1:="<D6"
Set rng = .AutoFilter.Range
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
.AutoFilterMode = False
End With

End Sub




DKY wrote:

Okay, I tried utilizing this code 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. I
don't understand, what Tom put works perfectly on my previous project.
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

Tom Ogilvy Wrote:
Sub DeleteData()
Dim rng As Range, rng1 As Range
Set rng = Range(Cells(2, "C"), Cells(Rows.Count, "C").End(xlUp))
For Each cell In rng
If cell.Value < 26 Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.EntireRow.Delete
End If
End Sub


or

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

Should do it.

--
Regards,
Tom Ogilvy



"DKY" wrote in message
...

Here's what I got, and needless to say attributing to my extreme lack
of
experience its not working. any suggestions?

Dim rng As Range, sStr As String, i As Long
Set rng = Cells(A, 65536).End(xlToLeft)
For i = rng.Row To 1 Step -1
sStr = LCase(Cells(1, i).Value)
If sStr < "26" Then
Cells(A, i).EntireRow.Delete
End If
Next


--
DKY

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


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

--

Dave Peterson