Hi there Connie,

I made a few adjustments to your code. You might want to try something like
this...

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range, rngDel As Range
Dim sh As Worksheet, n As Long
On Error GoTo ErrHandle
Call ToggleEvents(False)
Set sh = Worksheets("Compiled Totals")
Set rng = sh.Range(sh.Cells(9, "C"), sh.Cells(sh.Rows.Count,
"C").End(xlUp))
Set rngDel = sh.Range(sh.Cells(10, "C"), sh.Cells(sh.Rows.Count,
"C").End(xlUp))
sh.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:=sh.Range("oracle_no").Value,
Operator:=xlOr, Criteria2:="23356"
n = rngDel.SpecialCells(xlCellTypeVisible).Rows.Count
If n = 0 Then
MsgBox "No records matched your criteria!", vbInformation, "NO
RECORDS"
GoTo ErrHandle
Else
If MsgBox("Do you wish to delete " & n & " records?", vbYesNo,
"DELETE " & n & " RECORDS?") < vbYes Then
GoTo ErrHandle
End If
End If
rngDel.SpecialCells(xlCellTypeVisible).EntireRow.D elete
ErrHandle:
sh.AutoFilterMode = False
Call ToggleEvents(True)
End Sub

Sub ToggleEvents(blnState As Boolean)
With Application
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState = True Then
.CutCopyMode = False
.StatusBar = False
End If
End With
End Sub

Let us know how it works for you. HTH

--
Regards,
Zack Barresse, aka firefytr


"Connie" wrote in message
ups.com...
I am using the following code to delete rows in a range.


Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim sh As Worksheet

Set sh = Worksheets("Compiled Totals")
Set rng = sh.Range(sh.Cells(9, "C"), _
sh.Cells(sh.Rows.Count, "C").End(xlUp))
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Sheets("Compiled
Totals").Range(rng.Address).SpecialCells(xlCellTyp eVisible)
rng.EntireRow.Delete
Sheets("Compiled Totals").Range("C9").AutoFilter
End Sub

My data is as follows in column C, and the oracle_no I'm testing is
23356. The code above works, however, I not only delete all the rows
in which oracle_no = 23356, but I also delete the row right above the
first 23356 (where oracle_no = 23709).

Is there another way to do this? The autofilter function seems a
little quirky, and I want to make sure I delete the correct rows.

Also, if I wanted to count the number of rows for which oracle_no =
23356, how would I do that? I want to display that value in a message
prompt to the user, letting them know that they are requesting to
delete x number of records. Thanks!

Connie

"Oracle ID #"
23709
23709
23709
23709
23709
23709
23709
23356
23356
23356
23356
23356
23356
23356
23356
23356
23356
23356
23356
29697
29697
24898
24898
24898
24898
24898
24898
29697
29697
29697
29697
29697
29697
29697