0

I am getting 'Invalid procedure call or argument error..' when using the following code:

Dim DTTbl As ListObject
Dim rng As Range

Set DTTbl = ThisWorkbook.Worksheets("DataTypes").ListObjects("DataTypes")
Set rng = DTTbl.Range("DataTypes[[ClassProperty]:[DataType]]")

Note that both the worksheet and the table are named 'DataTypes'. The columns 'ClassProperty' & 'DataType' are adjacent.

How can I set the rng to the 'combined area/range' of the 2 columns mentioned using the column names?

aik3e
  • 131
  • 6
  • *"worksheet and the table are named 'DataTypes'"* I recommend to always use unique names. If you use the same names that will soon or later cause issues. • I recommend to access the ListObject properly instead of `Range("DataTypes[[ClassProperty]:[DataType]]")` use the technique I described [here](https://stackoverflow.com/a/43541508/3219613). – Pᴇʜ Apr 09 '21 at 09:56
  • Thanks @Pᴇʜ. I agree, its best to use unique names. Before posting the question I found similar questions with similar answers to the techniques on the link you provided but I find that they describe at best a single column selection. Paul's answer below provides better solution to the problem. – aik3e Apr 09 '21 at 10:48

1 Answers1

2

You can do it like this:

Dim col1 As Range, col2 As Range
Set col1 = DTTbl.ListColumns("ClassProperty").DataBodyRange
Set col2 = DTTbl.ListColumns("DataType").DataBodyRange

Set rng = ThisWorkbook.Worksheets("DataTypes").Range(col1.Address, col2.Address)
Debug.Print rng.Address
Paul Kelly
  • 975
  • 7
  • 13