Delete data validation

[drop05]

Hello, i am wanting to see if there is vba code that can delete data validations from cells.

I have a data validation on cell D66 on "Sheet 1". This validation is repeated every 3 columns over so like an offset from column D of 3, Column G, J, M, etc.
Is there something VBA to delete these validations in row 66 starting in column D and going every 3 columns?

Thank you!

 

[Jaafar Tribak]

Something like this may work :

Sub Test()

    Dim oStartCell As Range, oCell As Range

    Set oStartCell = Range("D66")
    Set oStartCell = oStartCell.Resize(, Columns.Count - oStartCell.Column + 1).SpecialCells(xlCellTypeAllValidation)
    
    If Not oStartCell Is Nothing Then
        For Each oCell In oStartCell
            If oCell.Column Mod 3 = 1 Then
                oCell.Validation.Delete
            End If
        Next oCell
    End If

End Sub

 

[Jaafar Tribak]

Better to fully qualify the range with the sheet name in case you run the code when Sheet1 is not the active sheet.

Sub Test()

    Dim oStartCell As Range, oCell As Range

    Set oStartCell = Sheet1.Range("D66")
    Set oStartCell = oStartCell.Resize(, Sheet1.Columns.Count - oStartCell.Column + 1).SpecialCells(xlCellTypeAllValidation)
    
    If Not oStartCell Is Nothing Then
        For Each oCell In oStartCell
            If oCell.Column Mod 3 = 1 Then
                oCell.Validation.Delete
            End If
        Next oCell
    End If

End Sub

 

[drop05]

Better to fully qualify the range with the sheet name in case you run the code when Sheet1 is not the active sheet.

Sub Test()

    Dim oStartCell As Range, oCell As Range

    Set oStartCell = Sheet1.Range("D66")
    Set oStartCell = oStartCell.Resize(, Sheet1.Columns.Count - oStartCell.Column + 1).SpecialCells(xlCellTypeAllValidation)
   
    If Not oStartCell Is Nothing Then
        For Each oCell In oStartCell
            If oCell.Column Mod 3 = 1 Then
                oCell.Validation.Delete
            End If
        Next oCell
    End If

End Sub

I am having trouble understanding what the code is doing just because i have never used resize, and the columns count,

 

[drop05]

Better to fully qualify the range with the sheet name in case you run the code when Sheet1 is not the active sheet.

Sub Test()

    Dim oStartCell As Range, oCell As Range

    Set oStartCell = Sheet1.Range("D66")
    Set oStartCell = oStartCell.Resize(, Sheet1.Columns.Count - oStartCell.Column + 1).SpecialCells(xlCellTypeAllValidation)
   
    If Not oStartCell Is Nothing Then
        For Each oCell In oStartCell
            If oCell.Column Mod 3 = 1 Then
                oCell.Validation.Delete
            End If
        Next oCell
    End If

End Sub

Also, sorry i should have mentioned that the sheet is protected but those columns D, G J, etc are not protected. So when i rant he code it came with the error due to the protection of the sheet, so is there a way to make that work with that specific offset of it

Also i have some logic for a possible For loop so it knows how many times to loop through it

Dim num_ent As Integer
num_ent = swp.Cells(Rows.count, 2).End(xlUp).Row - 4

For i = 1 To num_ent

if that can help with the loop