On Error Resume Next If Cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then 'No validation Else Ws.visible = xlSheetVisible 'Make the worksheet visibleįor Each Cell In WsVisible = ws.visible 'Original visibility setting Print "Processing worksheet :: " & ws.Name
'On Error GoTo Error_Handler Dim ws As WorksheetĪpplication.DisplayAlerts = False For Each ws In Worksheetsĭebug. It may not be sold/resold or reposted on other sites (links ' back to this site are allowed).
' Website : ' Purpose : Remove all data validation from a workbook ' To make it compatible with SharePoint ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). '- ' Procedure : ClearAllDataValidation ' Author : Daniel Pineault, CARDA Consultants Inc. This is a brute force method, but it does work. As such, I put together the following routine to clean out any existing Data Validation from a workbook. But when you are working with 10s, 100s of worksheets with 100s, 1000s or rows/column it simply become unfeasible to even consider doing this manually. For a simple workbook, maybe and even then. Now the only source of help I could locate was an explanation that you could use the find utility to locate cells with data validation and the delete them one by one…. Isn’t it great that MS has made its own feature incompatible with its own software! Brilliant (again)! What is even better is they do not provide a tools to convert, render a file compatible. Have you tried to publish as a web app an Excel Workbook and received an error regarding Data Validation (as shown in the image below) stating “The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser.
0 kommentar(er)
