Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 07, 05:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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


  #2   Report Post  
Old June 14th 07, 05:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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?

  #3   Report Post  
Old June 14th 07, 07:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #4   Report Post  
Old June 14th 07, 09:42 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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?

  #5   Report Post  
Old June 14th 07, 09:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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?




  #6   Report Post  
Old June 14th 07, 09:58 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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

  #7   Report Post  
Old June 14th 07, 10:15 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,909
Default 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?


  #8   Report Post  
Old June 14th 07, 11:08 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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
  #9   Report Post  
Old June 15th 07, 08:33 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 39
Default 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).

  #10   Report Post  
Old June 15th 07, 12:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to sum select columns in all workseets within a workbook Jodie Excel Worksheet Functions 2 October 27th 09 08:46 PM
Macro won't select columns Vick Excel Discussion (Misc queries) 1 August 17th 07 02:17 AM
Select noncontiguous multiple columns by column number? c mateland Excel Programming 8 October 22nd 06 12:23 PM
deleting corresponding columns on a multiple select listbox [email protected] Excel Programming 0 July 25th 06 03:37 PM
VBA to select multiple columns Marvin Excel Programming 11 October 1st 04 02:35 PM


All times are GMT +1. The time now is 08:51 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017