macro that selects mulitple columns

[bernd.vd.berg]

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.

Would someone be so kind to create this?

Thanks in advance.

Bernd

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
Set myCell = Application.InputBox(Prompt:="Please click a cell", _
Type:=8).Cells(1)
On Error GoTo 0

If myCell Is Nothing Then
'user hit cancel, what should happen
Else
With ActiveSheet
Set myRng = Union(.Range("D:J"), _
.Range(myCell.Address).EntireColumn)
End With
myRng.Select
End If

End Sub