CODEWITHSUNDEEP
excelvbarange
Eduards
Eduards

Reputation: 68

VBA Range statement using active column

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?

Upvotes: 2

Views: 2647

Answers (2)

snenson
snenson

Reputation: 436

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)

Upvotes: 0

Amiga500
Amiga500

Reputation: 1275

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.

Upvotes: 1

Related Questions