Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
I need to open a master name list and delete rows from it based on the
content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
Look in vba help for FINDNEXT. There is a good example
-- Don Guillett SalesAid Software "ClemCadidlhoper" wrote in message ... I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
Try this.
Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
I appreciate your response. Now I hope it works
"Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
It looks good, but I questions. I guess I don't know how to make it work.
Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
I don't think I'd use an event to run this.
I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
"Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
If the two worksheets are formatted in different fonts, will that make the
program not run? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
What does this mean? "Object doesn't support this property or method."?
"Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
Nope.
ClemCadidlhoper wrote: If the two worksheets are formatted in different fonts, will that make the program not run? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
Please disregard the last post. I figured this out already.
"ClemCadidlhoper" wrote: What does this mean? "Object doesn't support this property or method."? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
It means that the code is trying to do something that can't be done.
You may want to be a little more specific--what line causes the error? In fact, you may want to post your current code. ClemCadidlhoper wrote: What does this mean? "Object doesn't support this property or method."? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
First let me appologize for the multiple posts. This time I have a question
that I have worked on for a while. It seems the program works, but it skips around and deletes about 1/2 of the rows I thought it might be because "sheet 2" comes to me with some of the names in red and others in black. but that doesn't explain it. It deleted the first one then skipped the next 3, deleted 3, skipped 5, deleted 3, skipped 1, deleted 3, skipped 2,.....etc......a couple places it skipped one and deleted one and skipped the next. I have tried looking thru the code and cannot see anything obvious to me, that would explain it. Any ideas? "ClemCadidlhoper" wrote: What does this mean? "Object doesn't support this property or method."? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
If it's deleting fewer than you expect, I would think that it's because there is
an actual difference between the cells that you think are the same. Maybe there are leading/trailing/extra embedded spaces. Maybe it's a spelling difference. If you could pick out the cells that should match (by just eyeballing it), you could use a formula: =sheet2!Axx=sheet1!Ayy =sheet2!Bxx=Sheet1!Byy My bet is one of those values will be false. ClemCadidlhoper wrote: First let me appologize for the multiple posts. This time I have a question that I have worked on for a while. It seems the program works, but it skips around and deletes about 1/2 of the rows I thought it might be because "sheet 2" comes to me with some of the names in red and others in black. but that doesn't explain it. It deleted the first one then skipped the next 3, deleted 3, skipped 5, deleted 3, skipped 1, deleted 3, skipped 2,.....etc......a couple places it skipped one and deleted one and skipped the next. I have tried looking thru the code and cannot see anything obvious to me, that would explain it. Any ideas? "ClemCadidlhoper" wrote: What does this mean? "Object doesn't support this property or method."? "Dave Peterson" wrote: I don't think I'd use an event to run this. I'd run it on demand--after I do all the updating. I'm not sure what you mean about getting it to work, but... If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ClemCadidlhoper wrote: It looks good, but I questions. I guess I don't know how to make it work. Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I need an event procedure to tell it to run when data is added to sheet 2. "ClemCadidlhoper" wrote: I appreciate your response. Now I hope it works "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
Dave, Could you explain the following code. I am just not getting the
programming from all of the places I have looked for instruction, but it seems that there is something in this code or not in this code, which is keeping this from working the way I need it to. For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete names from a list
The match is equivalent to this in a worksheet:
=match(1,(c2=c3:c88)*(d2=d3:d88),0) (well, depending on the addresses of those variables.) The c2=C3:c88 return an array of trues and falses. the d2=d3:d88 return an array of trues and falses. When you multiply the trues and falses together, you get another array of 1/0's. But to get a 1, both conditions had to be true. The =match(1,thatarrayof1/0,0) says to find the first 1 in that array--the first time both column C and D were equal to C2 and D2 on the same row. If it's an error, then there was no match. If the match returns a number, then there was a match. I don't know if this does what you want, though. ClemCadidlhoper wrote: Dave, Could you explain the following code. I am just not getting the programming from all of the places I have looked for instruction, but it seems that there is something in this code or not in this code, which is keeping this from working the way I need it to. For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell "Dave Peterson" wrote: Try this. Option Explicit Sub testme() Dim MstrWks As Worksheet Dim SecondWks As Worksheet Dim myCell As Range Dim MstrRng As Range Dim SecondRng As Range Dim DelRng As Range Dim res As Variant Dim myFormula As String Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") With MstrWks Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With With SecondWks Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) End With For Each myCell In MstrRng.Cells myFormula = "Match(1,(" & myCell.Address(external:=True) _ & "=" & SecondRng.Address(external:=True) & ")*(" _ & myCell.Offset(0, 1).Address(external:=True) _ & "=" & SecondRng.Offset(0, 1).Address(external:=True) _ & "),0)" res = Application.Evaluate(myFormula) If IsError(res) Then 'no match, do nothing Else If DelRng Is Nothing Then Set DelRng = myCell Else Set DelRng = Union(myCell, DelRng) End If End If Next myCell If DelRng Is Nothing Then 'do nothing Else Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete End If End Sub Change the worksheet names and workbook names in these lines: Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1") Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2") And I do a select the range instead of deleting it--nice for testing: Application.Goto DelRng.EntireRow 'DelRng.EntireRow.Delete Delete the .goto line and uncomment the next line after you've verified that it works ok. ClemCadidlhoper wrote: I need to open a master name list and delete rows from it based on the content of another worksheets. More specifically I need to have it look at the info in columns A and B and if that same information is in columns A and B of the second document, then delete the whole row from the first document. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looking up names in a list | Excel Worksheet Functions | |||
Delete rows based on multiple criterias | Excel Discussion (Misc queries) | |||
EXCEL - LINKS cannot easily get list of all links & names in book | Excel Worksheet Functions | |||
how do i print a list of names and dates in excel into a two-colu. | Excel Discussion (Misc queries) | |||
Setting up a random list from long list of names ? | Excel Discussion (Misc queries) |