How to select multiple cells in VBA script

Question

The statement If Target.Address = "$I$2" Then selects just the I2 cell.

How can I select the entire column instead of just one cell? Here is my code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$I$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

If Target.Address = "$I$2" Then does not select a cell. ... it only checks if Target's address is I2 — jsotola
– jsotola, Commented Oct 17, 2017 at 16:33

shrivallabha.redij · Accepted Answer · 2017-10-18 08:17:19Z

1

You probably want to check the entire column "I" instead of cell "I2". This condition shall be changed from

    If Target.Address = "$I$2" Then

to

    If Target.Column = 9 Then

So that macro will consider it for complete column.

edited Oct 18, 2017 at 8:17

answered Oct 17, 2017 at 16:01

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mk mcmahon Over a year ago

you're a genius! thank you. Is there a way to select the entire sheet? or more than one column?

shrivallabha.redij Over a year ago

If you want to do it for complete sheet then just remove this condition. You will have to explain more than one part by suitable example as code may differ.

mk mcmahon Over a year ago

"You will have to explain more than one part by suitable example as code may differ." - Could I write ` If Target.Column = 9,10 Then` ? Should I copy and paste the code one column at a time?

shrivallabha.redij Over a year ago

There are many ways to write this e.g. If Target.Column = 9 Or Target.Column = 10 Then However this may become complicated when you have many such conditions.

SJR Over a year ago

@cucombrehombre - please accept the answer if it has solved your problem.

|

Collectives™ on Stack Overflow

How to select multiple cells in VBA script

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related