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

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

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

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

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

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

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

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

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

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

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

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

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