You could split the UsedRange on the worksheet into 3 or 4 parts
and use the SpecialCells method on each separate part.
That should eliminate the 8192 area limit.
To test whether that can/will cure the problem,
you can just change one line of your code...
Change:
Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
To:
Set rngDV = Selection.SpecialCells(xlCellTypeAllValidation)
Then select say a 1/4 of the cells and run the code.
--
Jim Cone
Portland, Oregon USA
"Gareth"
wrote in message
jim
Thanks for that. My sheet can have up to 20,000 cells with validation. Are
you able to amend my macro so that it will look at all dv cells instead of
only the first 8,000 or so just in case it is this that is causing the
program error?
Thanks.
Gareth
"Jim Cone" wrote:
> Gareth,
> Your code worked for me on xl2002 (with a small number of validated cells).
> Some comments...
>
> SpecialCells has a limit of 8192 areas that it can return.
> If you have thousands of validated cells on your worksheet you may be over the limit.
>
> It is usually best to loop thru each area in the range returned by SpecialCells...
> For Each rArea in ws.Cells.SpecialCells(xlCellTypeAllValidation).Areas
> For Each rCell in rArea.Cells
>
> If the problem is limited to just one workbook, it could be corrupt.
> You would need to build a new workbook.
>
> xl2007 has its own rule book designed to encourage users to upgrade from older versions.
> --
> Jim Cone
> Portland, Oregon USA
> "Gareth"
> wrote in message
> Hello all,
> I have been using the code below to count the number of data validation
> errors on a sheet (datasheet). It has worked sucessfully over a number of
> years but all of a sudden when it is run the following message is displayed
> and the file closes:
> EXCEL.exe has generated errors and will be closed by Windows.
> You will need to restart the program.
> An error log is being created.
>
> macro:
> Sub DVerrors()
> Application.ScreenUpdating = False
> Sheets("datasheet").Activate
> Dim rngDV As Range
> Dim cell As Range
> Dim countDV As Long
> Dim ws As Worksheet
> Set ws = ActiveSheet
> On Error GoTo errhandler
> Set rngDV = ws.Cells.SpecialCells(xlCellTypeAllValidation)
> On Error GoTo 0
> countDV = 0
> For Each cell In rngDV
> If Not cell.Validation.Value Then
> countDV = countDV + 1
> End If
> Next
> If countDV = 0 Then
> MsgBox "There are no data validation errors on the sheet.",
> vbInformation, "Invalid data entries"
> ActiveSheet.ClearCircles
> ElseIf countDV = 1 Then
> MsgBox "There is " & countDV & " data validation error on the sheet.",
> vbInformation, "Invalid data entry"
> ActiveSheet.CircleInvalid
> ElseIf countDV > 1 Then
> MsgBox "There are " & countDV & " data validation errors on the sheet.",
> vbInformation, "Invalid data entries"
> ActiveSheet.CircleInvalid
> End If
> Exit Sub
> errhandler:
> MsgBox "There are no data validation errors on the sheet.", vbInformation,
> "Invalid data entries"
> ActiveSheet.ClearCircles
> Application.ScreenUpdating = True
> End Sub
>
> Thanks in advance.
> Gareth
>
|
Similar Threads
