SpecialCells() fails

Add-in Express™ Support Service
That's what is more important than anything else

SpecialCells() fails
 
nitin c




Posts: 79
 Joined: 2007-05-18
Any idea why does the following code fail - I have empty worksheet 

Excel.Range blankRange = (ExcelApp.ActiveSheet as Excel._Worksheet).get_Range("A1", "A65536").SpecialCells(Excel.XlCellType.xlCellTypeBlanks,Excel.XlSpecialCellsValue.xlTextValues);


I get following exception 

System.Runtime.InteropServices.COMException was unhandled by user code
  HelpLink="D:\Program Files\Microsoft Office\OFFICE11\1033\xlmain11.chm"
  Message="No cells were found."
  Source="Microsoft Office Excel"
  ErrorCode=-2146827284
  StackTrace:
       at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       at Excel.Range.SpecialCells(XlCellType Type, Object Value)
       at *********.ExcelAddin.AddinModule.adxKeyboardShortcut1_Action(Object sender) in F:XlAddinSourceCodeAlpha2****************************AddinModule.cs:line 1641
       at AddinExpress.MSO.ADXKeyboardShortcut.DoAction()
Posted 25 Jun, 2007 09:23:42 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Nitin.

Please try to replace the 'A65536' with the 'A65535'



P.S. We always do our best to answer your forum requests as soon as possible. However, we apply the rule "first in first out" with Premium Support Service subscribers taking priority. Please understand it may take us some time to do research on the issue. Please be assured we will let you know as soon as the best possible solution is found.
Posted 25 Jun, 2007 11:11:56 Top
nitin c




Posts: 79
 Joined: 2007-05-18
I changed it to A65535 and get the following error

System.Runtime.InteropServices.COMException was unhandled by user code
HelpLink="D:\\Program Files\\Microsoft Office\\OFFICE11\\1033\\xlmain11.chm"
Message="No cells were found."
Source="Microsoft Office Excel"
ErrorCode=-2146827284
StackTrace:
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Excel.Range.SpecialCells(XlCellType Type, Object Value)
at Gridstone.ExcelAddin.AddinModule.adxKeyboardShortcut1_Action(Object sender) in F:\XlAddinSourceCode\Alpha2\********************\AddinModule.cs:line 1641
at AddinExpress.MSO.ADXKeyboardShortcut.DoAction()
Posted 26 Jun, 2007 09:08:17 Top
nitin c




Posts: 79
 Joined: 2007-05-18
As an alternative to this I tried the find function :

xlColumnAEmptyCell = columnA.Find(null, cellA1, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlNext, false, false);

This works, but it throws exception for 1 situation : if columnA is empty. So now i write some text in cells[1,1] and then the find works properly.

I also tired specialcells with some text in cells[1,1] but it still doesnt work.

Which is more efficient? Find or SpecialCells. I guess SpecialCells right?
Posted 26 Jun, 2007 09:37:55 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi Nitin.

Please use the UsedRange property of the Excel._Worksheet interface to obtain the available range.
Posted 26 Jun, 2007 14:11:44 Top