ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro to select multiple columns (https://www.excelbanter.com/excel-programming/391321-macro-select-multiple-columns.html)

bernd

Macro to select multiple columns
 
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

Macro to select multiple columns
 
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

Macro to select multiple columns
 
Check your other post for some ideas.

bernd wrote:

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


--

Dave Peterson

bernd

Macro to select multiple columns
 
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

Macro to select multiple columns
 
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

Macro to select multiple columns
 
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

Macro to select multiple columns
 
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

On Thu, 14 Jun 2007 13:42:18 -0700, bernd wrote:

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?



Dave Peterson

Macro to select multiple columns
 
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 wrote:

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


--

Dave Peterson

bernd

Macro to select multiple columns
 
@ 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

Macro to select multiple columns
 
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/lib...ffice.10).aspx
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

bernd wrote:

@ 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


All times are GMT +1. The time now is 11:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com