Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to sum select columns in all workseets within a workbook | Excel Worksheet Functions | |||
Macro won't select columns | Excel Discussion (Misc queries) | |||
Select noncontiguous multiple columns by column number? | Excel Programming | |||
deleting corresponding columns on a multiple select listbox | Excel Programming | |||
VBA to select multiple columns | Excel Programming |