Excel VBA On Error function not working

Question

I have some code in Excel VBA 7.1 using SpecialCells(xlCellTypeBlanks) method that should return an error if no blank cells are in the range. I have tried using:

• Wrapping the one line in an On Error Resume Next / Goto 0 (this used to work)
• Wrapping the one line in an On Error Goto ... / Goto 0
• IfError(....)
• Setting a range to the output of the SpecialCells hoping that it would return Nothing which I could then handle.

The Error Trapping is set to Unhandled Errors.

Any suggestions would be gratefully received.

Answer 1

My opinion...
SpecialCells works sometimes and sometimes not.

That's right, there are cases where SpecialCells fails. But to use a loop in general is not a wise advice either, because that is much slower in most cases.

We need to see a file to reproduce the issue.

Andreas.

Answer 2

Re: Special Cells

My opinion...
SpecialCells works sometimes and sometimes not.
I avoid using it.

You could run a loop on the range and check each cell using IsEmpty or

by checking the length of the cell formula. [edit]
If the cell IsEmpty or the length is 0 then exit the loop.

'---

Nothing Left to Lose

Answer 3

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.