How to Apply VBA Macro Code to Multiple Columns

rlovestheland

New Member
Joined
Nov 23, 2016
Messages
2
Hello! I have this code below applying to one column right now so that I can select multiple keywords from a data validation drop down. I need this to apply to multiple columns. I found a similar query on here but I couldn't get the code to work for me. Please help, see below.

I want it to work for column counts 3, 4, 5, 6, 7, 9, 10, 11 and 13 (C, D, E, F, G, I, J, K, M)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If


exitHandler:
Application.EnableEvents = True
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If I understand correctly, replace the
Code:
If Target.Column = 3 Then
with
Code:
    Select Case target.Column
    Case 3, 4, 5, 6, 7, 9, 10, 11, 13
and appropriate changes to the corresponding else and end if as in
Code:
    Case Else
        End Select
Hello! I have this code below applying to one column right now so that I can select multiple keywords from a data validation drop down. I need this to apply to multiple columns. I found a similar query on here but I couldn't get the code to work for me. Please help, see below.

I want it to work for column counts 3, 4, 5, 6, 7, 9, 10, 11 and 13 (C, D, E, F, G, I, J, K, M)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If


exitHandler:
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
If I understand correctly, replace the
Code:
If Target.Column = 3 Then
with
Code:
    Select Case target.Column
    Case 3, 4, 5, 6, 7, 9, 10, 11, 13
and appropriate changes to the corresponding else and end if as in
Code:
    Case Else
        End Select

Where in the end if series do I include the Case Else, End Select? I can't seem to get the loop to close correctly. Thank you!!
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top