Click here to Skip to main content
12,629,569 members (28,204 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

Steps:-
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:
$A$1:$D$4

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:
$A$1

Output Expected:
$A$1:$D$4

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

Output Received:
$B$1:$B$2,$A$1:$A$3

Output Expected:
$A$1:$D$4

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:
$A$1:$D$4

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

1 solution

Rate this: bad
 
good
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:

http://msdn.microsoft.com/en-us/library/office/bb178148%28v=office.12%29.aspx[^]

And in some cases youll get an exeption:
http://social.msdn.microsoft.com/Forums/eu/vsto/thread/f7459dbc-0737-4d42-a5dd-011cc1c25206[^]

The range documentation:
http://msdn.microsoft.com/en-us/library/office/ff196157.aspx[^]

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.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161205.3 | Last Updated 1 Aug 2012
Copyright © CodeProject, 1999-2016
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