Macro to delete on condition
 

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 figured it out!
 

Okay, disregard the previous post. I figured out my mistake. It had been so
long, I forgot that I had to change the sheet name in the macro each time I
ran the macro. Duh!

"KingdomGirl" wrote:

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 figured it out!
 

On Sep 22, 9:53*am, KingdomGirl
wrote:
Okay, disregard the previous post. *I figured out my mistake. *It had been so
long, I forgot that I had to change the sheet name in the macro each time I
ran the macro. *Duh!

It's fun to be able to figure things out, but if I understand you then
it sounds like you are doing too much work if you need to modify the
code 19 times in order to do what you want to do. Perhaps you could
replace the section of code

With Sheets("WK")
(...)
End With

by

For Each ws In Sheets
If ws.Name < Sheets("Criteria").Name Then
With ws
(...)
End With
End If
Next ws


(here ws is of course a declared worksheet variable and (...) is the
body of the with statement)

hth

-scattered

I figured it out!
 

Oh, thank you! I'll definitely give that a try. I don't know much about
macros (obviously), but I'm learning.

Thanks again.

"scattered" wrote:

On Sep 22, 9:53 am, KingdomGirl
wrote:
Okay, disregard the previous post. I figured out my mistake. It had been so
long, I forgot that I had to change the sheet name in the macro each time I
ran the macro. Duh!

It's fun to be able to figure things out, but if I understand you then
it sounds like you are doing too much work if you need to modify the
code 19 times in order to do what you want to do. Perhaps you could
replace the section of code

With Sheets("WK")
(...)
End With

by

For Each ws In Sheets
If ws.Name < Sheets("Criteria").Name Then
With ws
(...)
End With
End If
Next ws


(here ws is of course a declared worksheet variable and (...) is the
body of the with statement)

hth

-scattered