CODEWITHSUNDEEP
excelvba
James
James

Reputation: 1

VBA excel - using a variable to select a column range

VBA - The user selects a number from a combobox (1-50) and it is assigned as a variable. I now want to program a function which selects columns BA to a column to the left whatever value the user selected (I.E. from AV:BA where AV is the variable column). I have the variable the user slects as a string (dim var as string). Your help would be appreciated. Thanks

Upvotes: 0

Views: 12100

Answers (1)

Ben Hoffstein
Ben Hoffstein

Reputation: 103345

The OFFSET property is what you're looking for, although to give you a full answer it would be helpful if you posted the code you've written thus far.

Here is some more info about how OFFSET works:

http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm

Edit: Here is a quick and dirty example to get you going. In this case, the SelectColumns subroutine takes a single parameter which tells it how many columns to the left of BA should be selected (along with BA). If you execute the Test subroutine, you'll see that columns AY:BA get selected on the active worksheet.

Sub SelectColumns(numColsToLeft As Integer)
    Range(Range("BA1").EntireColumn, Range("BA1").Offset(0, -numColsToLeft).EntireColumn).Select
End Sub

Sub Test()
    Call SelectColumns(2)
End Sub

Upvotes: 2

Related Questions