Loop through xlCellTypeAllValidation

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I understand that I can loop thru .xlCellTypeAllValidation using a For Each loop; however, I'm looking to do things a bit more efficiently, specifically is there a way to group all similar validations into a single range using either .Areas or .xlCellTypeSameValidation. .xlCellTypeSameValidation does this but only once. How do I find ALL the ranges that have the same validations as a composite/Union range??

For example, I create five different validation ranges; if the ranges are non-contiguous, .Areas provides a list of each separate contiguous range. However, it does not discriminate by different validations, even if the two validation ranges are contiguous. I guess I'm looking for a analogous function as .Areas provides for non-contiguous ranges but for data validation. Make sense??

Thanks much, y'all.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,215,477
Messages
6,125,037
Members
449,205
Latest member
Eggy66

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