VBA Range statement using active column

Question

I need to use my active column in my range statements. How to write down this:

Range((Activecolumn)2)

So I need a column value to be dynamic because I am moving left and right by pressing a userform button, but the row number always stays the same. At first I am working with column "C" and select it with

Columns("C").Select

and I navigate the selected column with

Private Sub Next_Format_Button_Click()

ActiveCell.Offset(, 1).EntireColumn.Select

End Sub

So when I navigate to the next column I need all my other range statements to go along with it. So basically the column letter in my range statements should be something like "current" or "active" column.

Could someone, please, help me?

Do you just need a variable where you get the number of the column you are in? — snenson
– snenson, Commented Mar 23, 2021 at 8:35
That is what I was looking for but in this example I was looking for "C1"not "C2" so that "2" in the code should be "1". But besides that - it's what I needed — Eduards
– Eduards, Commented Mar 23, 2021 at 9:33
How would it look if my rows range is "C4:C6" because "Cells(4:6, ActiveCell.Column)" seems to be incorrect as it highlights red in VBA editor — Eduards
– Eduards, Commented Mar 23, 2021 at 9:42

Amiga500 · Accepted Answer · 2021-03-23 08:46:59Z

1

Range(ActiveCell.address).entireColumn.copy

Is what you are looking for I think?

I'd also recommend avoiding ".Select" unless absolutely necessary. It is adding unnecessary lines to your code and leaves the program at greater risk of inadvertent user corruption by selecting another range via mouse before the copy (or whatever) operation completes. Better to go straight to whatever operation you intend to do, or by assigning the range to a variable.

edit: Ah, sorry, I see now your only looking row 2, then its Cells(2, ActiveCell.Column) as pointed out above.

edited Mar 23, 2021 at 8:46

answered Mar 23, 2021 at 8:41

Amiga500

1,2851 gold badge7 silver badges12 bronze badges

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

1 Comment

Eduards Over a year ago

There's no possibility to accidentally select wrong place in the sheet as all the actions are happening in the userform and changes are instantly propagated to the sheet. Sheet has disabled editing and all that, but it's handy to see the active column selected from user standpoint

snenson · Accepted Answer · 2021-03-23 08:49:29Z

0

If you want to get the Number of the column where you selected something:

ActiveCell.Column

If you need the letter of the column your selection is in:

Split(ActiveCell.Address, "$")(1)

answered Mar 23, 2021 at 8:49

snenson

4361 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

VBA Range statement using active column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related