Select first choice in a validation list in a range that are not the same

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have a range of validation lists that needs to be reset to their first value (its a text string not numerical value).

Imagine if the validation lists in the range can be set to any number from 0-20 then you could reset it by the following simple macro:
Sub X()
Range("G16:G22").Value = "0"
End Sub

But in a range of validation lists where the first choice is not the same the above simple macro does not work, also it is text not numbers.
Also some cells in the range does not contain a validation list and is just a normal cell with text.

How can I make a macro that resets the range to the first selection in all the validation lists in the range, while I still have some none validation cells ?
 
What happens if you don't change the comma in the Split?
It does the same as in the test sheet it will where it will show the entire Data Validation string (first picture in last post).
Also, it only seems to effect the cell G22 for some reason.
Also, another note is that the VLOOKUP data is in another sheet, not in the same sheet like in the test file.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I end up hard coding it like this

Sub Reset_size()
Dim Cell As Range

Set Cell = Cells(16, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(17, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(18, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(19, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(20, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(21, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(22, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(24, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(25, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(29, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(31, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(33, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(39, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(40, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(41, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(42, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(43, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(44, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(45, 6)
Cell.Value = Range(Split(Mid(Cell.Validation.Formula1, 2), ";")(0)).Value

Set Cell = Cells(46, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(47, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(48, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(49, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(51, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(52, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(53, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(54, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(55, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(56, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(57, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(58, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(59, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(60, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(61, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(62, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(64, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(65, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(66, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(71, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(72, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(73, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(77, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

Set Cell = Cells(78, 6)
Cell.Value = Split(Cell.Validation.Formula1, ";")(0)

End Sub


with Reset Data Validation Lists to first item, method range has failed
for reference
 
Upvote 0
What sort of validation to you have in F45 that you need to use this?
VBA Code:
Set Cell = Cells(45, 6)
Cell.Value = Range(Split(Mid(Cell.Validation.Formula1, 2), ";")(0)).Value
As far as I can see that appears to be the only validation that differs from the others.
 
Upvote 0
This is the one where the data validation list comes from a table in another sheet (picture in last post) & has a dependable vlookup function in another cell tied to it.
There used to be more of these cells but my data changed.

Also, as you can see I skipped some numbers which are the ones that are just normal cells with text.
 
Upvote 0
Can you post the formula for that validation?
 
Upvote 0
vlookup cell
1608113226584.png

data validation cell
1608113241709.png
 
Upvote 0
I added a similar data validation and the original code I posted worked.:eek:
 
Upvote 0
It's probably something else in the excel file that ****s it up. Because your code also worked in the test excel file that I posted.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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