Click here to Skip to main content
11,921,792 members (67,838 online)
Rate this:
Please Sign up or sign in to vote.
See more: MS-Excel VBA
I have written a macro to find blank cells from a certain range using SpecialCells method of excel range object.
When i am trying to execute following code i get a exception as "No cells found".

Sub test()
Debug.Print Sheet1.Range("A1:D4").SpecialCells(xlCellTypeBlanks).Address
End Sub

1. Open a new Excel instance.
2. Press ALT + F11/ Open VBE
3. Insert a new Module
4. Paste the above code and run test macro as specified above.

Output Received:
Runtime Error '1004'. No Cells Found.

Output Expected:

Case 1:
Now if i do any operation of cell A1. Operation such as giving fill color, etc. Call the test() macro then it does not throw exception.

Output Received:

Output Expected:

Case 2:
Suppose if i give any value in cell B3. Call the test() macro, exception is not thrown.

Output Received:

Output Expected:

Case 3:

If i try to edit value or fill the cell outside the range "A1:D4" for example E10 and execute test() method then it gives me the proper output.

Output Received:

Note: Execute each Case with a new excel instance.
Posted 31-Jul-12 21:21pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Im no expert in Special cells but it looks as you are making some "special" use of them according to the documentation:[^]

And in some cases youll get an exeption:[^]

The range documentation:[^]

If you still cant make it work you should post you question at the designated VSTO forum [^] as most programemrs are not used to Excel manipulation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 493
1 Dave Kreskowiak 220
2 phil.o 170
3 Sergey Alexandrovich Kryukov 157
4 KrunalRohit 134
0 OriginalGriff 7,036
1 KrunalRohit 4,530
2 Sergey Alexandrovich Kryukov 3,286
3 George Jonsson 2,840
4 Suvendu Shekhar Giri 2,216

Advertise | Privacy | Mobile
Web04 | 2.8.151120.1 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100