none
How to determine ListRow a cell is contained in? RRS feed

  • Question

  • Hi,

    I have a single cell selected inside a table (listobject). I want to select the listrow that the cell is part of.

    Is there a straight forward way of doing this? I am using VBA with Excel 2010.

    Regards

    Asif


    Thursday, November 22, 2012 10:59 AM

Answers

  • By the time I was ready to post this I see that Learning and Learning has already posted a good answer. However, as my solution uses a different approach (including testing to ensure that the selection is within the DataBodyRange of the table) I thought I should post it anyway.

    The testing tests for a single row selected (not necessarly a single cell) and so long as the activecell of the selection is within the DataBodyRange then it works.

    Sub Macro1()
        Dim lstObj As ListObject
        Dim rngIsect As Range
        Dim lngListRow As Long
           
        Set lstObj = ActiveSheet.ListObjects("Table1")
       
        If Selection.Rows.Count > 1 Then
            MsgBox "Select from one row only within the ListObject." & _
                    vbCrLf & "Processing terminated."
            Exit Sub
        End If
           
        With lstObj
            'Test if the ActiveCell of the Selection is within the ListObject DataBodyRange
            Set rngIsect = Intersect(ActiveCell, .DataBodyRange)
            If Not rngIsect Is Nothing Then
               
                'Identify the ListRow number
                lngListRow = .ListRows(ActiveCell.Row - .HeaderRowRange.Row).Index
               
                'Select the Listrow number
                .DataBodyRange.Rows(lngListRow).Select
            Else
                MsgBox "Selected range not within the ListObject DataBodyRange"
            End If
        End With
    End Sub


    Regards, OssieMac

    Thursday, November 22, 2012 1:18 PM
  • Sub SelectListRow()
    
        Dim lFirstCol As Long
        Dim lLastCol As Long
        Dim lActiveCol As Long
        
        Dim rng As Range
        
        Set rng = ActiveCell
        
        lActiveCol = rng.Column
        
        With rng.ListObject
            lFirstCol = .ListColumns(1).Range.Column
            lLastCol = .ListColumns(.ListColumns.Count).Range.Column
        End With
        
        rng.Offset(, -(lActiveCol - lFirstCol)) _
            .Resize(, lLastCol - lFirstCol + 1).Select
    
    End Sub
    This can help you...

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, November 22, 2012 12:43 PM
    Answerer

All replies

  • Sub SelectListRow()
    
        Dim lFirstCol As Long
        Dim lLastCol As Long
        Dim lActiveCol As Long
        
        Dim rng As Range
        
        Set rng = ActiveCell
        
        lActiveCol = rng.Column
        
        With rng.ListObject
            lFirstCol = .ListColumns(1).Range.Column
            lLastCol = .ListColumns(.ListColumns.Count).Range.Column
        End With
        
        rng.Offset(, -(lActiveCol - lFirstCol)) _
            .Resize(, lLastCol - lFirstCol + 1).Select
    
    End Sub
    This can help you...

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, November 22, 2012 12:43 PM
    Answerer
  • By the time I was ready to post this I see that Learning and Learning has already posted a good answer. However, as my solution uses a different approach (including testing to ensure that the selection is within the DataBodyRange of the table) I thought I should post it anyway.

    The testing tests for a single row selected (not necessarly a single cell) and so long as the activecell of the selection is within the DataBodyRange then it works.

    Sub Macro1()
        Dim lstObj As ListObject
        Dim rngIsect As Range
        Dim lngListRow As Long
           
        Set lstObj = ActiveSheet.ListObjects("Table1")
       
        If Selection.Rows.Count > 1 Then
            MsgBox "Select from one row only within the ListObject." & _
                    vbCrLf & "Processing terminated."
            Exit Sub
        End If
           
        With lstObj
            'Test if the ActiveCell of the Selection is within the ListObject DataBodyRange
            Set rngIsect = Intersect(ActiveCell, .DataBodyRange)
            If Not rngIsect Is Nothing Then
               
                'Identify the ListRow number
                lngListRow = .ListRows(ActiveCell.Row - .HeaderRowRange.Row).Index
               
                'Select the Listrow number
                .DataBodyRange.Rows(lngListRow).Select
            Else
                MsgBox "Selected range not within the ListObject DataBodyRange"
            End If
        End With
    End Sub


    Regards, OssieMac

    Thursday, November 22, 2012 1:18 PM
  • Thanx a lot! 

    It worked for em! :)

    Tuesday, November 27, 2012 10:23 AM