Select 3 of 6 list columns?

[beckyinDC]

This works:

ActiveSheet.ListObjects("REQSDATATABLE").ListColumns(1).DataBodyRange.Select

but I was hoping to select 3 columns at once like this (which doesnt work):

ActiveSheet.ListObjects("REQSDATATABLE").ListColumns(1, 2, 3).DataBodyRange.Select

I also tried 1:3

So just looking for how to select 3 of 6 columns of a listobject. ?

It seems odd to have to deal with all 3 separately, thoguh I could do that. I have filtered the list, and want to format borders on the first 3 rows which may have 1 to many relationship with the next three- so borders would help show that...I find if I make the right selection any formatting changes would just grab the rows displayed...which is what I want.

I was not able to find solution googling and searching here for syntax...possibly missed it.

I am using win 2007

 

[beckyinDC]

Using resize- this worked since they were contiguous columns...

ActiveSheet.ListObjects("REQSDATATABLE").ListColumns(1).DataBodyRange.Select
Selection.Resize(Selection.rows.Count, 3).Select

Seems kludgey...feels like there must be a better approach-

If there is a better way, would appreciate it- thanks

 

[Rick Rothstein]

This seems a little kludgy too; however this approach will allow you to select any columns (contiguous or not) within your ListObject...

Sub SelectListObjectColumns()
  Const RelativeColumnsToSelect As String = "A:A,D:D,F:F"
  With ActiveSheet.ListObjects("List1").DataBodyRange
    Intersect(.Cells, Range(RelativeColumnsToSelect).Offset(, .Column - 1)).Select
  End With
End Sub

The RelativeColumnsToSelect constant controls which columns you want to select. Note that the column designations are relative to the first row of the ListObject; so A:A refers to the Columns 1 of the ListObject, B:B refers to Column 2 of the ListObject, and so on, not matter where the actual ListObject is located on the worksheet.

 

[Rick Rothstein]

Here is a little more verbose code that allows you to refer to the ListObject columns by their number (use the ColumnsToSelect constant to specify the column numbers you want)...

Sub SelectListObjectColumns()
  Dim X As Long, U As Range, Cols() As String
  Const ColumnsToSelect As String = "1,3,6"
  Cols = Split(ColumnsToSelect, ",")
  With ActiveSheet.ListObjects("REQSDATATABLE").DataBodyRange
    For X = 0 To UBound(Cols)
      If U Is Nothing Then
        Set U = Intersect(.Cells, Columns(Val(Cols(X))).Offset(, .Column - 1))
      Else
        Set U = Union(U, Intersect(.Cells, Columns(Val(Cols(X))).Offset(, .Column - 1)))
      End If
    Next
    U.Select
  End With
End Sub

Note: In my previously posted code, I called the ListObject by the default name of List1 instead of your specified name of REQSDATATABLE.

 

[beckyinDC]

Thanks Rick! I am surprised that Excel doesnt allow comma delimination within that refence to the ListColumns collection...but thank you for your confirmation of that and the workarounds presented- very much appreciated!

 

[Rick Rothstein]

Thanks Rick! I am surprised that Excel doesnt allow comma delimination within that refence to the ListColumns collection...but thank you for your confirmation of that and the workarounds presented- very much appreciated!

I've never understood why Microsoft choose to allow the Range property to take non-contiguous column ranges using a comma delimited set of column letters separated by commas, for example Range("A:C,E:E"), but not to allow the Columns property to do an equivalent using either Columns("A:C,E") or Columns("1:3,5")... it seems to me like this would have been a logical (and parallel structured) thing to have done. But, alas, Microsoft didn't.

 

[pgc01]

...
but I was hoping to select 3 columns at once like this (which doesnt work):

ActiveSheet.ListObjects("REQSDATATABLE").ListColumns(1, 2, 3).DataBodyRange.Select

I also tried 1:3
...

Hi

This should do it:

ActiveSheet.ListObjects("REQSDATATABLE").DataBodyRange.Columns("A:C").Select

or

ActiveSheet.ListObjects("REQSDATATABLE").DataBodyRange.Columns(1).Resize(, 3).Select

Remark: you don't usually use Select in vba. It's normally unnecessary, inefficient and hurts readability.