K
KingdomGirl
Hi!
I hope someone can help me. I work at a school and have an excel workbook
titiled "Test Scores". There are 19 worksheets in the workbook, with each
worksheet representing a subject area, e.g. Alg I, Geo, Chem, etc. For each
subject area, rows contain data representing the student's ID#, name, & test
score.
Last year, I created a macro to delete the records of students that had
graduated. I inserted a sheet titled "criteria" and then ran the following
macro:
Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Criteria")
Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With
'Loop through the cells in the Criteria range
For Each cell In CriteriaRng
With Sheets("WK")
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=cell.Value
With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
Next cell
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
It worked great last year, and now it doesn't work at all, and I can't
figure out why. Any help you can give me would be greatly appreciated.
I hope someone can help me. I work at a school and have an excel workbook
titiled "Test Scores". There are 19 worksheets in the workbook, with each
worksheet representing a subject area, e.g. Alg I, Geo, Chem, etc. For each
subject area, rows contain data representing the student's ID#, name, & test
score.
Last year, I created a macro to delete the records of students that had
graduated. I inserted a sheet titled "criteria" and then ran the following
macro:
Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Sheets("Criteria")
Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With
'Loop through the cells in the Criteria range
For Each cell In CriteriaRng
With Sheets("WK")
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=cell.Value
With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
Next cell
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
It worked great last year, and now it doesn't work at all, and I can't
figure out why. Any help you can give me would be greatly appreciated.