Excel VBA - increment column reference in range

Question

How do I increment the column reference in the code below? Just the column ref, the row values can stay the same.

Range("w3:w54").PasteSpecial

I'm hoping I can do something like this:

For i = 23 to 27
'start at column w (which is number 23)

Range(Columns(i)+"3":Columns(i)+"54").PasteSpecial  

Next

But that throws an error.

Any suggestions?

Shai Rado · Accepted Answer · 2017-05-31 09:10:04Z

3

You can use the Cells(Row, Column), so you increment the Column part easily with the numeric value, without using a conversion function from Numeric to Alphabetical.

For i = 23 To 27
    'start at column w (which is number 23)
    Range(Cells(3, i), Cells(24, i)).PasteSpecial
Next

answered May 31, 2017 at 9:10

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

2 Comments

Boosted_d16 Over a year ago

Exactly what I wanted! Thanks a lot for your time.

Dirk Reichel Over a year ago

Also ...Range("3:24").Columns(i)... is possible while paste will extend the range as it needs to. In most cases also ...Cells(3, i).Paste will do the job ;)

Collectives™ on Stack Overflow

Excel VBA - increment column reference in range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related