I have a named range, that I would like to search for a name, and then delete
the record (row) in that range that contains the name I found.

I can do a Range.Find to find the name, but it returns me a Address of the
cell on the spreadsheet. I was wondering how would I find the location of the
cell relative to the Range, rather than relative to the worksheet.

If I used the Macro Recorder to right click on the cell containing the name,
and choose "Delete Table Row", I get the following code:
Selection.ListObject.ListRows(61).Delete

The problem with that is that the 61, is the row number relative to the
named range, and I don't know how to get that programatically.

Any help would be appreciated.

Thanks,
Steve

If your range is named "Steve" and the text you are looking for is "Haack"
then:

Sub demo()
For Each r In Range("Steve")
If r.Value = "Haack" Then
r.EntireRow.Delete
Exit Sub
End If
Next
End Sub


will remove the row with that text in it.
--
Gary's Student
gsnu200702


"Steve Haack" wrote:

I have a named range, that I would like to search for a name, and then delete
the record (row) in that range that contains the name I found.

I can do a Range.Find to find the name, but it returns me a Address of the
cell on the spreadsheet. I was wondering how would I find the location of the
cell relative to the Range, rather than relative to the worksheet.

If I used the Macro Recorder to right click on the cell containing the name,
and choose "Delete Table Row", I get the following code:
Selection.ListObject.ListRows(61).Delete

The problem with that is that the 61, is the row number relative to the
named range, and I don't know how to get that programatically.

Any help would be appreciated.

Thanks,
Steve

Duh, that was pretty simple....

Thanks a bunch.

"Steve Haack" wrote:

I have a named range, that I would like to search for a name, and then delete
the record (row) in that range that contains the name I found.

I can do a Range.Find to find the name, but it returns me a Address of the
cell on the spreadsheet. I was wondering how would I find the location of the
cell relative to the Range, rather than relative to the worksheet.

If I used the Macro Recorder to right click on the cell containing the name,
and choose "Delete Table Row", I get the following code:
Selection.ListObject.ListRows(61).Delete

The problem with that is that the 61, is the row number relative to the
named range, and I don't know how to get that programatically.

Any help would be appreciated.

Thanks,
Steve

OK, then another (probably) dumb question....
When I am looking at R.Value, I can find the name that I am searching for.
But let's say, that once I find the one I am looking for, I want to then look
at other cells on the same row, within the range "Steve" before deleteing
that row? How would I reference the other cells on the same row in that range?

"Gary''s Student" wrote:

If your range is named "Steve" and the text you are looking for is "Haack"
then:

Sub demo()
For Each r In Range("Steve")
If r.Value = "Haack" Then
r.EntireRow.Delete
Exit Sub
End If
Next
End Sub


will remove the row with that text in it.
--
Gary's Student
gsnu200702


"Steve Haack" wrote:

I have a named range, that I would like to search for a name, and then delete
the record (row) in that range that contains the name I found.

I can do a Range.Find to find the name, but it returns me a Address of the
cell on the spreadsheet. I was wondering how would I find the location of the
cell relative to the Range, rather than relative to the worksheet.

If I used the Macro Recorder to right click on the cell containing the name,
and choose "Delete Table Row", I get the following code:
Selection.ListObject.ListRows(61).Delete

The problem with that is that the 61, is the row number relative to the
named range, and I don't know how to get that programatically.

Any help would be appreciated.

Thanks,
Steve

Sub demo2()
For Each r In Range("Steve")
If r.Value = "Haack" Then
i = r.Row
MsgBox ("the row is " & i)
MsgBox (Cells(i, 1).Value & " is in the first cell in the row")
MsgBox (Cells(i, 2).Value & " is in the second cell in the row")
Exit Sub
End If
Next



--
Gary's Student
gsnu200702


"Steve Haack" wrote:

OK, then another (probably) dumb question....
When I am looking at R.Value, I can find the name that I am searching for.
But let's say, that once I find the one I am looking for, I want to then look
at other cells on the same row, within the range "Steve" before deleteing
that row? How would I reference the other cells on the same row in that range?

"Gary''s Student" wrote:

If your range is named "Steve" and the text you are looking for is "Haack"
then:

Sub demo()
For Each r In Range("Steve")
If r.Value = "Haack" Then
r.EntireRow.Delete
Exit Sub
End If
Next
End Sub


will remove the row with that text in it.
--
Gary's Student
gsnu200702


"Steve Haack" wrote:

I have a named range, that I would like to search for a name, and then delete
the record (row) in that range that contains the name I found.

I can do a Range.Find to find the name, but it returns me a Address of the
cell on the spreadsheet. I was wondering how would I find the location of the
cell relative to the Range, rather than relative to the worksheet.

If I used the Macro Recorder to right click on the cell containing the name,
and choose "Delete Table Row", I get the following code:
Selection.ListObject.ListRows(61).Delete

The problem with that is that the 61, is the row number relative to the
named range, and I don't know how to get that programatically.

Any help would be appreciated.

Thanks,
Steve