A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Macro to select multiple columns



 
 
Thread Tools Display Modes
  #1  
Old June 14th 07, 05:45 PM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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

Ads
  #2  
Old June 14th 07, 05:56 PM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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  
Old June 14th 07, 07:03 PM posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old June 14th 07, 09:42 PM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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  
Old June 14th 07, 09:45 PM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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  
Old June 14th 07, 09:58 PM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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  
Old June 14th 07, 10:15 PM posted to microsoft.public.excel.programming
Gord Dibben
external usenet poster
 
Posts: 22,912
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  
Old June 14th 07, 11:08 PM posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old June 15th 07, 08:33 AM posted to microsoft.public.excel.programming
bernd
external usenet poster
 
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  
Old June 15th 07, 12:41 PM posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07: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 10:47 AM.


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