Get a first column range from a range in Excel VBA

Question

I would like to get a range of the first column from a larger range. For example:

If the range is $E$9:$I$259, the result should be $E$9:$E259

How can I achieve this?

JvdV · Accepted Answer · 2020-03-16 11:32:54Z

23

By using the columns collection of the range object like so:

Range("$E$9:$I$259").Columns(1)

edited Mar 16, 2020 at 11:32

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

answered Apr 12, 2013 at 14:52

Dave Sexton

11.2k4 gold badges46 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dmitrij Holkin Over a year ago

But how to get multiple columns Range("$E$9:$E259").Columns(1:3) ?

Dave Sexton Over a year ago

You can't do neat and tidy things like 1:3 in VBA and I don't think you would have 3 columns in the range E9:E259. Either be more selective in your range selection and use all columns or loop through the columns collection.

Patrick Lepelletier Over a year ago

with non contiguous range it fails. Had to sort an array created from range's adress, and then join it together first :

Dim TempA() , A as Range : TempA = Split(A, ",") :  QuickSort (TempA) :  Set A = A.Parent.Range(Join(TempA, ",")).Address :  Set Col= A.columns(1)

.

Dan Chaltiel Over a year ago

Didn't you mean Range("$E$9:$I$259").Columns(1) ?

Collectives™ on Stack Overflow

Get a first column range from a range in Excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related