xlcelltypeAllValidation not working

[shazab_rafiq]

so a bit of background as to what im trying to acheive here:

I want a peice of code that runs through all cells & finds every cell with a drop down in. Now the below seems to work but on occassions it doesnt show some cells that have validation(drop down) in them:

activesheet.usedrange.SpecialCells(xlCellTypeAllValidation).Address

any idea why the above is not working?

If anyone could suggest a better way of finding all drop down cells using VBA that would be great

 

[kevatarvind]

hi and welcome to the board

try like below

Sub Test()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation).Select
For Each cell In Selection
 n = n & "," & cell.Address
Next
MsgBox "Below Are The Cell Address Which having Validation" & vbCr & vbCr & n
End Sub

 

[shazab_rafiq]

hi and welcome to the board

try like below

Sub Test()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation).Select
For Each cell In Selection
 n = n & "," & cell.Address
Next
MsgBox "Below Are The Cell Address Which having Validation" & vbCr & vbCr & n
End Sub

Thank you so much!! worked perfectly.