Why can't similar code be applied to selecting multiple columns in a table?

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
387
Office Version
  1. 365
Platform
  1. Windows
Gurus,
I was using
VBA Code:
Sheets("a").ListObjects("abc").Range.rows("1:2").Select
but when i do
VBA Code:
Sheets("a").ListObjects("abc").Range.Columns("1:2").Select
it's not working anymore.
but when i do
VBA Code:
Sheets("a").ListObjects("abc").Range.Columns(1).Select
it's working again.
how come i can only select 1 column?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Note that these are NOT similar:
Rich (BB code):
Sheets("a").ListObjects("abc").Range.Columns("1:2").Select
and
Rich (BB code):
Sheets("a").ListObjects("abc").Range.Columns(1).Select

One has double-quotes around the column reference and the other does not.
The second is using an index (meaning the first column, regardless of name of the column.

If you want to see what code that selects the two column looks like, simply turn on your Macro Recorder and record yourself selecting the two columns.
 
Upvote 0
So why is selecting multiple rows working?
I tried recording macro but the form is like this:
VBA Code:
Range("a[[Column3]:[Column5]]").Select
I'm trying to use similar form like this:
VBA Code:
Sheets("a").ListObjects("abc").Range.rows("1:2").Select
Note that these are NOT similar:
Rich (BB code):
Sheets("a").ListObjects("abc").Range.Columns("1:2").Select
and
Rich (BB code):
Sheets("a").ListObjects("abc").Range.Columns(1).Select

One has double-quotes around the column reference and the other does not.
The second is using an index (meaning the first column, regardless of name of the column.

If you want to see what code that selects the two column looks like, simply turn on your Macro Recorder and record yourself selecting the two columns.
 
Upvote 0
When you use quote marks around the rows or columns of interest, you are referring to the labels of the rows or columns. Rows use numbers as labels and columns use letters as labels, just like in a worksheet. So to select the first two columns of a table you could use this.

VBA Code:
Sheets("a").ListObjects("abc").Range.Columns("A:B").Select
 
Upvote 0
When you use quote marks around the rows or columns of interest, you are referring to the labels of the rows or columns. Rows use numbers as labels and columns use letters as labels, just like in a worksheet. So to select the first two columns of a table you could use this.

VBA Code:
Sheets("a").ListObjects("abc").Range.Columns("A:B").Select
Is the Columns("A:B") in your example also relative position within the listobject table?
 
Upvote 0
Is the Columns("A:B") in your example also relative position within the listobject table?
Yes, just like it is for rows. The very first line of code you posted in post #1 above ..

I was using
VBA Code:
Sheets("a").ListObjects("abc").Range.rows("1:2").Select

.. would select G7:K8 in table "abc" below, even though that is in worksheet rows 7 and 8

yxz152830.xlsm
ABCDEFGHIJKL
1
2
3
4
5
6
7Hdr1Hdr2Hdr3Hdr4Hdr5
87213349218
92873329415
10733396914
118147467661
128962347119
131072316331
14182925785
15413691467
1645265706
178810333512
18235392277
19
a
 
Upvote 0
Solution
Solution for column Index relative position.

to select columns(1:2), try like this:

Sheets("a").ListObjects("abc").ListColumns(1).DataBodyRange.Resize(, 2).Select
 
Upvote 0
Furthermore, in general, to select non or contiguous columns

Union(mylistObjects.Range.Columns(1), mylistObjects.Range.Columns(2)).Select
or
Union(mylistObjects.Range.Columns(1), mylistObjects.Range.Columns(3)).Select
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top