Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete dupes in one of two sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete dupes in one of two sheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete dupes in one of two sheets
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
delete dupes in column | Excel Programming |