Macro to select multiple columns

[bernd]

Hello you all,

I need a macro te select a couple of columns. The selection always
consists of column D to J. Now I want the macro to add a specific
column to this selection. The column is chosen by the user. It would
be nice if the user is promted a question which column should be
added
to the selection, based on the values that are in row 2.


Example
In row 2 of columns K,L and M are the values:


K L M
Toyota Renault Ford


If the user selects Renault, column L has to be added to the
selection.

I've no programming skills, so would someone be so kind to create
this?

Thanks in advance.


Bernd

 

[bernd]

And if possible,

Can this macro be made so that not only one, but also two or all three
of the columns can be selected?

 

[Dave Peterson]

Check your other post for some ideas.

Hello you all,

I need a macro te select a couple of columns. The selection always
consists of column D to J. Now I want the macro to add a specific
column to this selection. The column is chosen by the user. It would
be nice if the user is promted a question which column should be
added
to the selection, based on the values that are in row 2.

Example
In row 2 of columns K,L and M are the values:

K L M
Toyota Renault Ford

If the user selects Renault, column L has to be added to the
selection.

I've no programming skills, so would someone be so kind to create
this?

Thanks in advance.

Bernd

 

[bernd]

With this piece of code I can create my selection.

Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("D1:J1")
Set r2 = ActiveCell
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select
End Sub

The problem that is stil left is the that of the ActiveCell. I want
the user to choose where that should be (which column)
I've tried:
Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Dim iColumn As Variant
iColumns = InputBox("Which column to add to the selection?")
Set r1 = Range("D1:J1")
Set r2 = ActiveCell
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select

But this doesn't work right. I want the user to select the column that
should be added to the selection. Can someone help me out here?

 

[bernd]

With this piece of code I can create my selection.

Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("D1:J1")
Set r2 = ActiveCell
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select
End Sub


The problem that is stil left is that of the ActiveCell. I want
the user to choose where that should be (which column)
I've tried:

Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Dim iColumn As Variant
iColumn = InputBox("Which column to add to the selection?")
Set r1 = Range("D1:J1")
Set r2 = ActiveCell
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select


But this doesn't work right. I want the user to select the column
that
should be added to the selection.
Can someone help me out here?

 

[bernd]

I got the code working, but don't think it's user friendly. Right now
the column number should be given by the user.
I would prefer the letter of the column or even better a value that is
selected by some kind of listbox.
Someone?

Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Dim vResult As Variant
vResult = Application.InputBox( _
Prompt:="Number of columns to copy:", _
Title:="Copy Columns", _
Type:=1, _
Default:=1)
If vResult = False Then Exit Sub 'user cancelled
Application.Goto (Cells(1, vResult))
Set r1 = Range("D1:J1")
Set r2 = ActiveCell
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select
End Sub

 

[Gord Dibben]

Sub MultiRange()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("D1:J1")
Set r2 = Application.InputBox(Prompt:= _
"Select Desired Column", Type:=8)
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.EntireColumn.Select
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

You have a few different active branches in this thread.

I'm not sure if you have a solution--or what to start with if you don't.

 

[bernd]

@ Gord Ribbon: do I understand your solution right that after calling
the macro the user has to click on the column that has to be added to
the selection? Instead of typing in the column letter?

@ Dave Peterson: With my and Gord Ribbon's solution I'm able to select
what I want. But there's still a problem with the inputbox. Most
preferrable I would like the user to be prompted a combobox or
listbox. Then select a name that is found the column that has to be
added to the selection. And let the application go to that cell, so
the activecell is in the column I want to add to the selection. In
this way I can use my solution above.

Another possibility is the let the user type in the letter of the
column (like K or L).

 

[Dave Peterson]

Maybe it's time to learn about UserForms.

Check Debra Dalgleish's site:
http://contextures.com/xlUserForm01.html

and these articles by Peter Aiken:
http://msdn2.microsoft.com/en-us/library/aa155729(office.10).aspx
http://msdn2.microsoft.com/en-us/library/aa155610(office.10).aspx