| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Delete Rows
I would like to be able to delete a row with a name in it based on a
cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
This macro:
Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
That worked great, thank you so much.
How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Just enter the new name in the same cell and run the macro again.
-- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
I want your macro to delete a row in 4 diffferent sheets using a name
in a cell from another sheet all at the same time. Bernie Gary''s Student wrote: Just enter the new name in the same cell and run the macro again. -- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Now I understand:
Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub First it goes to A1 in Sheet1 to get the value. Then it goes to all the other sheets in the workbook and looks for that value. If it finds the value, it removes the row and moves on to the next sheet -- Gary's Student "Bernie" wrote: I want your macro to delete a row in 4 diffferent sheets using a name in a cell from another sheet all at the same time. Bernie Gary''s Student wrote: Just enter the new name in the same cell and run the macro again. -- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
I don't want it to look at all the sheets, just 5. How do I name them?
Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub First it goes to A1 in Sheet1 to get the value. Then it goes to all the other sheets in the workbook and looks for that value. If it finds the value, it removes the row and moves on to the next sheet -- Gary's Student "Bernie" wrote: I want your macro to delete a row in 4 diffferent sheets using a name in a cell from another sheet all at the same time. Bernie Gary''s Student wrote: Just enter the new name in the same cell and run the macro again. -- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
It is easier to list the sheets to avoid. Replace
If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub First it goes to A1 in Sheet1 to get the value. Then it goes to all the other sheets in the workbook and looks for that value. If it finds the value, it removes the row and moves on to the next sheet -- Gary's Student "Bernie" wrote: I want your macro to delete a row in 4 diffferent sheets using a name in a cell from another sheet all at the same time. Bernie Gary''s Student wrote: Just enter the new name in the same cell and run the macro again. -- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Or if many sheets in workbook may be easier to put the 5 you want into an array.
If many sheets you could put the 5 sheets in an array For Each W In Worksheets(Array("sheet1", "Sheet3", _ "Sheet5", "sheet7", "sheet9")) Gord Dibben MS Excel MVP On Thu, 28 Dec 2006 15:00:00 -0800, Gary''s Student wrote: It is easier to list the sheets to avoid. Replace If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Thank you. I get the error message of Run Time Error 438. What do I
do? Bern Gord Dibben wrote: Or if many sheets in workbook may be easier to put the 5 you want into an array. If many sheets you could put the 5 sheets in an array For Each W In Worksheets(Array("sheet1", "Sheet3", _ "Sheet5", "sheet7", "sheet9")) Gord Dibben MS Excel MVP On Thu, 28 Dec 2006 15:00:00 -0800, Gary''s Student wrote: It is easier to list the sheets to avoid. Replace If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Thank you very much for all of your assistance.
I am going to have too many sheets to avoid. I would rather name the sheets I want to scan. Thanks, Bern Gary''s Student wrote: It is easier to list the sheets to avoid. Replace If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub First it goes to A1 in Sheet1 to get the value. Then it goes to all the other sheets in the workbook and looks for that value. If it finds the value, it removes the row and moves on to the next sheet -- Gary's Student "Bernie" wrote: I want your macro to delete a row in 4 diffferent sheets using a name in a cell from another sheet all at the same time. Bernie Gary''s Student wrote: Just enter the new name in the same cell and run the macro again. -- Gary's Student "Bernie" wrote: That worked great, thank you so much. How do I run the same macro for another name right after this one? Bernie Gary''s Student wrote: This macro: Sub remover() v = Sheets("Sheet1").Range("A1").Value For Each r In Sheets("Sheet2").UsedRange If r.Value = v Then r.EntireRow.Delete Exit Sub End If Next End Sub will get the value in cell A1 of Sheet1. It will then go to Sheet2, locate the first cell with that value and delete the entire row. -- Gary's Student "Bernie" wrote: I would like to be able to delete a row with a name in it based on a cell with that name from another cell in a different worksheet. Any ideas? Thanks, Bernie |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Don't know where you have entered the code.
Did you plug it into the code provided by Gary's Student? Post the code you're using. Gord On 29 Dec 2006 06:24:45 -0800, "Bernie" wrote: Thank you. I get the error message of Run Time Error 438. What do I do? Bern Gord Dibben wrote: Or if many sheets in workbook may be easier to put the 5 you want into an array. If many sheets you could put the 5 sheets in an array For Each W In Worksheets(Array("sheet1", "Sheet3", _ "Sheet5", "sheet7", "sheet9")) Gord Dibben MS Excel MVP On Thu, 28 Dec 2006 15:00:00 -0800, Gary''s Student wrote: It is easier to list the sheets to avoid. Replace If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Delete Rows
Actually they all didn't work. I am looking for all new code that
works. :) Thanks, Bernie Gord Dibben wrote: Don't know where you have entered the code. Did you plug it into the code provided by Gary's Student? Post the code you're using. Gord On 29 Dec 2006 06:24:45 -0800, "Bernie" wrote: Thank you. I get the error message of Run Time Error 438. What do I do? Bern Gord Dibben wrote: Or if many sheets in workbook may be easier to put the 5 you want into an array. If many sheets you could put the 5 sheets in an array For Each W In Worksheets(Array("sheet1", "Sheet3", _ "Sheet5", "sheet7", "sheet9")) Gord Dibben MS Excel MVP On Thu, 28 Dec 2006 15:00:00 -0800, Gary''s Student wrote: It is easier to list the sheets to avoid. Replace If w.Name = "Sheet1" Then with If w.Name="Sheet1" or w.Name="avoid3" or w.Name="avoid9" Then -- Gary''s Student "Bernie" wrote: I don't want it to look at all the sheets, just 5. How do I name them? Bernie Gary''s Student wrote: Now I understand: Sub remover2() v = Sheets("Sheet1").Range("A1").Value For Each w In Worksheets If w.Name = "Sheet1" Then Else For Each r In w.UsedRange If r.Value = v Then r.EntireRow.Delete Exit For End If Next End If Next End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| easy way to delete all rows with no text in them? | New Users to Excel | |||
| delete rows | Excel Discussion (Misc queries) | |||
| Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
| Want to delete rows | Excel Discussion (Misc queries) | |||
| How can we delete rows permanently from excel sheet | Excel Discussion (Misc queries) | |||
Linear Mode
