How to remove data validation from the specified range?

14 Oct 20242 minutes to read

You can remove data validation from the specified range using the Clear method with the ExcelClearOptions of ClearDataValidations option.

The following code example demonstrates how to remove data validation from the specified range.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    FileStream inputStream = new FileStream("InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Removes data validation from the specified range           
    worksheet.Range["A1:C5"].Clear(ExcelClearOptions.ClearDataValidations);

    //Saving the workbook as stream
    FileStream OutputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
    workbook.SaveAs(OutputStream);
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Removes data validation from the specified range           
    worksheet.Range["A1:C5"].Clear(ExcelClearOptions.ClearDataValidations);

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Removes data validation from the specified range
    worksheet.Range("A1:C5").Clear(ExcelClearOptions.ClearDataValidations)

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using