Deleting rows in a range using Autofilter

[Connie]

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(xlCellTypeVisible)
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

 

[Zack Barresse]

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.Delete
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

 

[Zack Barresse]

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.Delete
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

 

[Connie]

Thanks Zack so much for your response! I have been working all night
trying to get this thing to work, but haven't been successful. I tried
your idea of setting a "rngDel", but in some cases this would work and
in others I would miss one of the rows which met the criteria. Just
curious -- what is the logic behind setting this range (i.e., is there
something about setting the AutoFilter function that requires you be
one row below the first row of data?)

Since I couldn't get the rngDel to work properly, I took it out and am
using the following code. The other problem I have is that I keep
getting the "Run time error 1004- Application Defined or object defined
error" on the line below marked with an asterik (*), so for testing
purposes to see if the code would work, I hard coded the criteria.
Note that Oracle_no is a defined name on the "Field_Rep_Time_Sheet"
sheet (Insert->Name->Define). I'm not sure why I keep getting the 1004
error.

Any additional help you can give would be greatly appreciated!

Connie

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim rngDel As Range
Dim sh As Worksheet
Dim n As Long

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
rng.AutoFilter field:=1, Criteria1:="24898"
VisibleDropDown = False

n = rng.SpecialCells(xlCellTypeVisible).Rows.Count
If n = 0 Then
MsgBox "No records matched your criteria!", vbInformation, "NO
", "RECORDS "
Call ErrHandle(sh)
Exit Sub
Else
If MsgBox("Do you wish to delete " & n & " records?", vbYesNo,
"DELETE " & n & " RECORDS?") <> vbYes Then
Call ErrHandle(sh)
Exit Sub
End If
End If
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
MsgBox "You have successfully deleted " & n & "records for employee
" & employee_name
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

Sub ErrHandle(sh As Worksheet)
sh.AutoFilterMode = False
Call ToggleEvents(True)
End Sub