Selected row in a List

[NicolaasH]

Hi,

I would like to ask the following:

How do I detect in which row of a list (ListObject) a selected cell is?

Purpose:
I would like to enable the user to select a cell or row in a list and then they have the option of 2 buttons:
Duplicate Row - the row gets copied and added at the bottom of the list
Delete Row - the row is deleted

The duplication and deleting is not a problem, however I do not know how I can detect which row number in the list has been selected/activated.

Thanks!
Nick

 

[Bong]

The ListObject has the ListRows property that returns a ListRows collection. If the row of your cell is in that collection, you can use the Index property to find the index number in the List.

_________________
Bob Rashkin

 

[NicolaasH]

Bob, thanks, but could you help me out a little more? I'm not sure how to extract the index number yet, I guess that was actually my question. I now have the following code:

Public Sub DuplicateRow()
Dim ListWorksheet As Worksheet, objList1 As ListObject
Dim SelectedRow As Integer

Set ListWorksheet = ThisWorkbook.Worksheets("List")
Set objList1 = PipelineWorksheet.ListObjects(1)

On Error GoTo WrongSelection
If Not objList1.Active Then GoTo WrongSelection

'determine selected row
'part that does not work yet

SelectedRow = objList1.ListRows.Index

'rest of code
'...
'...

AddRow CopyRow:=SelectedRow, Duplication:=True

Exit Sub
WrongSelection:
MsgBox Prompt:="Duplication Failed", Buttons:=vbCritical, Title:="Duplication Error"

End Sub

...so what shall I put after 'part that does not work yet'.
Thanks!

 

[AnotherHiggins]

If the user will first select the cell, why not just use
[tab]Activecell.entirerow
or
[tab]Activecell.entirecolumn
?



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[NicolaasH]

Yes, I had something like this before,

ActiveCell.EntireRow.Cells(1, 1).Row

however this returns the row in the sheet, while I am looking for the row in the List, which is a different index different.

 

[AnotherHiggins]

I still don't see the problem.

Let's say the user presses the duplicate button. Assuming that your data in the list is contiguous, then try this:

ActiveCell.EntireRow.Copy _
    Destination:=ActiveCell.End(xlDown).Offset(1).EntireRow

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[NicolaasH]

Unfortunately it is a problem, because it is not merely copying and pasting. I need the ListRow reference to find a couple of other parameters (this happens in the 'rest of code bit, and the procedure AddRow that is called).

To keep the code neat and not run into problems when I move the list, I do not want to define the listrow by something like listrow = absolute reference - 6

 

[NicolaasH]

I have found a solution, be it not a very aesthetic one. I have first determined the absolute row reference of the top of the list and than subtract this from the absolute reference of the selected cell.

ListPosition = objList1.Range(1, 1).Row
AbsoluteRow = ActiveCell.EntireRow.Cells(1, 1).Row
SelectedRow = AbsoluteRow - ListPosition

if anyone knows a better way of referencing the row index, please let me know.

 

[stefen054]

Hi

May be following code help you

Sub check()
Dim objList As ListObject
Dim sh As Worksheet
Dim rng As Range
' i have the list object in sheet1
Set sh = ThisWorkbook.Sheets("Sheet1")
With sh
If .ListObjects(1).Active Then
Set rng = Intersect(ActiveCell, .ListObjects(1).Range)
Debug.Print rng.Row
Debug.Print rng.Column
End If
End With
End Sub

stefen