Applying VBA to multiple range

abesacto916

New Member
Joined
Mar 6, 2016
Messages
2
Hey all,

I'm trying to teach myself VBA and this forum has been extremely useful. I've been using the code below, which I found online. The only issue I have is applying the code to from B2:B100. I juts cannot seem to have it apply to all the cells and I'm lost. I created a Data Validation List Box and this code allows me to select multiple choices, but I need this to occur from B2:B100.

Help!!!!!:)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from Welcome to Trump Excel » Trump Excel
' To Select Multiple Items from a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$B$3" 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
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True

Exitsub:
Application.EnableEvents = True

End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
Replace the $B$3 line with:
Code:
    If Not Intersect(Target, Range("B2:B100")) Is Nothing Then
Please use CODE tags when posting VBA code - the # icon in the message editor.
 

abesacto916

New Member
Joined
Mar 6, 2016
Messages
2
Awesome, that worked. What would be the best way to omit B50 from being included and then recommencing on B51?

I'll be sure to use the CODE tags next time.

Thanks for your help, it's greatly appreciated!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
You may try this:
But be aware sheet change events can be tricky and may result in some looping problems.
I'm not a big fan of anything that happens automatically unless I'm really sure what I'm doing.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B100")) Is Nothing Then
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
What would be the best way to omit B50 from being included and then recommencing on B51?

Code:
    If Not Intersect(Target, Union(Range("B2:B49"), Range("B51:B100"))) Is Nothing Then
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,305
Messages
5,600,867
Members
414,410
Latest member
4610

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
Top