Find and Delete Row from Range
 

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

Find and Delete Row from Range
 

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

Find and Delete Row from Range
 

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

Find and Delete Row from Range
 

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

Find and Delete Row from Range
 

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