1

I try to select multiple columns out of a named range. I build the string what should be selected with some variables:

Dim onecolumn as Integer, lastcolumn as Integer
Dim tblTable as ListObject
Dim strforrange as String

Set tblTable = Sheet1.ListObjects("Table1")
onecolumn = 3
lastcolumn = tblTable.Range.Columns.Count 

strforrange = tblTable & "[[#All],[Column" & onecolumn & "]:[Column" & lastcolumn - 1 & "]]"

Range(strforrange).Select

I ran the code step-by-step and checked the "strforrange" variable. Even when it's correct the selected columns differ from the ones stated in the string.

Is there any other way to get the range of a named table or does someone know a solution for this?

freakfeuer
  • 98
  • 1
  • 8

1 Answers1

1

The following should help; I've included a reference to a helpful function which converts a column number to the corresponding column letter (credit: Function to convert column number to letter? answer by @brettdj)

Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

Using this function you can just use the DataBodyRange function alongside Columns to select the desired range:

Sub SelectSpecificDataRange

Dim startColumn as Long, lastColumn as Long
Dim tblTable as ListObject
Dim startColumnLetter, lastColumnLetter as String

Set tblTable = Sheet1.ListObjects("Table1")
startColumn = 3
startColumnLetter = Col_Letter(startColumn)
lastColumn = tblTable.Range.Columns.Count
lastColumnLetter = Col_Letter(lastColumn)

tblTable.DataBodyRange.Columns(startColumnLetter & ":" & lastColumnLetter).Select

End Sub
Community
  • 1
  • 1
Jordan
  • 4,424
  • 2
  • 18
  • 32
  • Thank you for your answer, it fixed it! In your code is a small mistake: The variables startColumnLetter and lastColumnLetter should not be enclosed with double quotes. – freakfeuer Apr 22 '16 at 11:41
  • Glad I could help! Oh yes, i'll edit that now. Thanks – Jordan Apr 22 '16 at 11:45