Select up to last used cell in ListColumn

[eirikdaude]

I have a ListObject looking like this, and want to select B4:B16 for ListColumn(1), C4:C13 for ListColumn(2) and D4:D13 for ListColumn(3).

Is there any quick way to do this?

I know I can use

ActiveWorksheet.ListObjects(1).ListColumn(1).DataBodyRange

to select ListColumn(1), but what is the smartest way to find the last used cell in the two other columns?

 

[DanteAmor]

want to select B4:B16 for ListColumn(1), C4:C13 for ListColumn(2) and D4:D13 for ListColumn(3).

Is there any quick way to do this?

Maybe there is another way, but I show you an option:

Sub selectdata_1()
  Dim f As Range
  Dim i As Long, lr1 As Long, lr2 As Long, lr3 As Long
   
  Set f = Range("B:B").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  lr1 = f.Row
  Set f = Range("C:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  lr2 = f.Row
  Set f = Range("D:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  lr3 = f.Row
 
  i = ActiveSheet.ListObjects(1).DataBodyRange.Cells(1, 1).Row
  ActiveSheet.Range("B" & i & ":B" & lr1 & ",C" & i & ":C" & lr2 & ",D" & i & ":D" & lr3).Select
End Sub

Or this:

Sub Macro7()
    ActiveSheet.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeConstants, 23).Select
End Sub

 

[eirikdaude]

Maybe there is another way, but I show you an option:

Thanks. It's not quite what I was hoping for, but it does do what I want.

 

[Peter_SSs]

Why are you trying to select those ranges?
I am asking because generally in vba there is no need to actually select cells/ranges to work with them and selecting can slow your code considerably.

 

[Alex Blakenburg]

You need to address Peter's question on why you want to select the range but see it this is any closer to what you were hoping for.

Sub GetLastUsedRowInColumn()
    Dim ws As Worksheet
    Dim cellLastUsed As Range
    Dim rngCol2 As Range, rngCol3 As Range
    
    Set ws = ActiveSheet

    With ws.ListObjects(1)
        With .ListColumns(2)
            Set cellLastUsed = .Range.Find( _
                       What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set rngCol2 = ws.Range(.DataBodyRange.Cells(1, 1), cellLastUsed)
        End With
        
        With .ListColumns(3)
            Set cellLastUsed = .Range.Find( _
                       What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set rngCol3 = ws.Range(.DataBodyRange.Cells(1, 1), cellLastUsed)
        End With
        
        Debug.Print rngCol2.Address, rngCol3.Address
        ' Syntax to select
        rngCol2.Select
        rngCol3.Select

    End With

End Sub

 

[eirikdaude]

Why are you trying to select those ranges?
I am asking because generally in vba there is no need to actually select cells/ranges to work with them and selecting can slow your code considerably.

To know how many rows I need to allocate when copying the values from the ListObject to another range. It could be I chose my words poorly when I wrote that I wanted to select the range up to the last used cell, as I am not making an actual select-statement.

What I ended up using was something like this:

    With Innstillingar.ListObjects(1)
        Set rngCol2 = .DataBodyRange.Cells(.DataBodyRange.Rows.Count, 2)
        If IsEmpty(rngCol2) Then
            Set rngCol2 = rngCol2.End(xlUp)
        End If
        Set rngCol2 = Range(.DataBodyRange.Cells(1, 2), rngCol2)
    End With

 

[DanteAmor]

What I ended up using was something like this:

The following does the same as your code:

  With Innstillingar.ListObjects(1).ListColumns(2)
    Set rngCol2 = Range(.Range.Cells(2), Columns(.Range.Column).Find("*", , xlValues, 2, 1, 2))
  End With