Two SS class attendance sheets. Sheet 2 contains names of JrHi, Sheet 1
contains names of grade school students.
Need to delete names of students in Sheet 1 that show up in Sheet 2

Gordy99

Gordy99 wrote:
Two SS class attendance sheets. Sheet 2 contains names of JrHi, Sheet 1
contains names of grade school students.
Need to delete names of students in Sheet 1 that show up in Sheet 2

Gordy99

Hi Gordy,

Try this macro out on a backup copy of your data.

Student names that appear in column A (starting in row 2, assuming row
1 is a heading)
of Sheet1 and Sheet2 are deleted from Sheet1. The entire row is deleted
and rows below are shifted up.

Public Sub DeleteStudents()
Application.ScreenUpdating = False
'Change the value of the constant HeadingRows to suit
'your needs
Const HeadingRows As Long = 1
Dim lLastRow1 As Long
Dim lLastRow2 As Long
Dim rngNames2 As Range
Dim lRows1 As Long
Dim rngCell2 As Range
lLastRow1 = Sheet1.Range("A" & _
Sheet1.Range("A:A").Rows.Count).End(xlUp).Row
lLastRow2 = Sheet2.Range("A" & _
Sheet2.Range("A:A").Rows.Count).End(xlUp).Row
Set rngNames2 = Sheet2.Range(Sheet2.Cells(HeadingRows + 1, 1), _
Sheet2.Cells(lLastRow2, 1))
For lRows1 = lLastRow1 To HeadingRows + 1 Step -1
For Each rngCell2 In rngNames2
If Sheet1.Cells(lRows1, 1).Value = _
rngCell2.Value Then
Sheet1.Cells(lRows1, 1).EntireRow.Delete _
shift:=xlUp
Exit For
End If
Next rngCell2
Next lRows1
End Sub

If the number of rows taken up by column headings is greater than 1
then just change the value of the constant HeadingRows.

Ken Johnson

"Ken Johnson" wrote:

Gordy99 wrote:
Two SS class attendance sheets. Sheet 2 contains names of JrHi, Sheet 1
contains names of grade school students.
Need to delete names of students in Sheet 1 that show up in Sheet 2

Gordy99
Thanks. I'll see if I can make it work. It looks like it should. I'll post a
thankyou later.


Hi Gordy,

Try this macro out on a backup copy of your data.

Student names that appear in column A (starting in row 2, assuming row
1 is a heading)
of Sheet1 and Sheet2 are deleted from Sheet1. The entire row is deleted
and rows below are shifted up.

Public Sub DeleteStudents()
Application.ScreenUpdating = False
'Change the value of the constant HeadingRows to suit
'your needs
Const HeadingRows As Long = 1
Dim lLastRow1 As Long
Dim lLastRow2 As Long
Dim rngNames2 As Range
Dim lRows1 As Long
Dim rngCell2 As Range
lLastRow1 = Sheet1.Range("A" & _
Sheet1.Range("A:A").Rows.Count).End(xlUp).Row
lLastRow2 = Sheet2.Range("A" & _
Sheet2.Range("A:A").Rows.Count).End(xlUp).Row
Set rngNames2 = Sheet2.Range(Sheet2.Cells(HeadingRows + 1, 1), _
Sheet2.Cells(lLastRow2, 1))
For lRows1 = lLastRow1 To HeadingRows + 1 Step -1
For Each rngCell2 In rngNames2
If Sheet1.Cells(lRows1, 1).Value = _
rngCell2.Value Then
Sheet1.Cells(lRows1, 1).EntireRow.Delete _
shift:=xlUp
Exit For
End If
Next rngCell2
Next lRows1
End Sub

If the number of rows taken up by column headings is greater than 1
then just change the value of the constant HeadingRows.

Ken Johnson