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
Add a comment
|
1 Answer
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
2 Comments
James
yes but then do i put in the variable in the offset? Columns("-str:BA").Select str is my variable
James
Public Sub SampleBox_Change() Dim str As Integer If (SampleBox.ListIndex > -1) Then str = SampleBox.List(SampleBox.ListIndex) End If End Sub Public Sub Samplesdel(str As Integer) Range(Range("BA1").EntireColumn, Range("BA1").Offset(0, -str).EntireColumn).Select End Sub Public Sub CommandButton1_Click() Application.Run "Samplesdel" End Sub