Error “Select method of Range class failed” when selecting range in VB.Net Excel workbook

R

rsmith19

New Member
Joined
Jul 11, 2016
Messages
2
(First time poster, so please let me know if I did anything wrong with the question formatting/placement/content. Thanks)

Brief intro: I'm working on an Excel workbook that was developed by someone else at my company in Visual Studio 2010 for Excel 2007/2010. I'm now coming back with Visual Studio 2012 to add functionality to be used in Excel 2016.


Problem: At some point in the program, the code is supposed to open up a new Excel workbook and insert some new data in it. That all works fine, but I whenever I try to select a specific cell in that new workbook (Ex. wksht.Range("A1").Select), I get the error "Select method of Range class failed". I am activating the worksheet before trying to select a range in the new workbook. I know that selecting ranges should be avoided when possible, but this is old code from the previous developer that I am trying to leave intact. The interesting thing about this is that it works perfectly when I open the workbook in Excel 2010. The issue only comes up when I try to do the exact same thing with the exact same code in Excel 2016. We just now updated to 2016, so this is why we never encountered this error before. Is there a different way for selecting a range in Excel 2016 with VB.Net than is used for previous versions?


Error Message:

Code: 
<code>************** Exception Text **************
System.Runtime.InteropServices.COMException (0x800A03EC): Select method of Range class failed
   [location of line where error occurred, edited to preserve employer's property]

************** Loaded Assemblies **************
mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1076.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/mscorlib.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Office.Runtime
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Office.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Office.Runtime.dll
----------------------------------------
System.Core
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1075.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Configuration
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1067.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Security
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Security/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Security.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.Hosting
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Hosting/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Hosting.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.Runtime
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.Runtime/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.Runtime.dll
----------------------------------------
System.Deployment
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Deployment/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Deployment.dll
----------------------------------------
Microsoft.VisualStudio.Tools.Applications.ServerDocument
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualStudio.Tools.Applications.ServerDocument/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Tools.Applications.ServerDocument.dll
----------------------------------------
System.Windows.Forms
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1068.2 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Xml.Linq
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll
----------------------------------------
Microsoft.Office.Tools
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.dll
----------------------------------------
Microsoft.Office.Tools.Excel.Implementation
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Excel.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Excel.Implementation.dll
----------------------------------------
Microsoft.Office.Tools.Common.Implementation
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common.Implementation/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.Implementation.dll
----------------------------------------
WindowsBase
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/WindowsBase/v4.0_4.0.0.0__31bf3856ad364e35/WindowsBase.dll
----------------------------------------
PresentationFramework
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1080.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/PresentationFramework/v4.0_4.0.0.0__31bf3856ad364e35/PresentationFramework.dll
----------------------------------------
PresentationCore
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1080.0 built by: NETFXREL3STAGE
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_64/PresentationCore/v4.0_4.0.0.0__31bf3856ad364e35/PresentationCore.dll
----------------------------------------
Microsoft.Office.Tools.Excel
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Excel/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Excel.dll
----------------------------------------
Microsoft.Office.Tools.Common
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.60301.0
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.Office.Tools.Common/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.Office.Tools.Common.dll
----------------------------------------
Microsoft.Office.Tools.Excel.v4.0.Utilities
    Assembly Version: 10.0.0.0
    Win32 Version: 10.0.30319.1
    CodeBase: file:///C:/Users/[yo my name]/Documents/Test%20Bed/Microsoft.Office.Tools.Excel.v4.0.Utilities.DLL
----------------------------------------
Microsoft.VisualBasic
    Assembly Version: 10.0.0.0
    Win32 Version: 14.6.1055.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll</code>

Code w/ problem (wksht is fed as Excel.Worksheet to this function):

Code: 
<code>                'AUTO SIZE ROWS
                wksht.Activate()
                For iRow = valRowDataStart To (valRowDataStart + iValidCount)
                    wksht.Range("A" & iRow).EntireRow.Select()

                    wksht.Range(valColLocation & iRow).WrapText = True
                    wksht.Range(valColLoadsControlled & iRow).WrapText = True

                    wksht.Range("A" & iRow).EntireRow.AutoFit()
                    wksht.Range("A" & iRow).RowHeight = wksht.Range("A" & iRow).RowHeight + 5
                Next iRow</code>

Like I said earlier, this is my first time posting a question, so please let me know if I did anything wrong or if you just need more information. Thank you so much for you help!
 
Sort by date Sort by votes
Why are you using Select/Activate in this code?

It's rarely, if ever, needed to select/activate to work with ranges/sheets/workbooks etc.

Have you tried just removing these lines?
Code: 
wksht.Activate()

wksht.Range("A" & iRow).EntireRow.Select()
 
Upvote 0
I'm not sure why the previous developer put those lines in there to be honest. I did try removing them, and the code seemed to work.

The reason I went ahead and asked this question is because I wanted to reposition the cursor to the first data entry in the table a little bit later in the code. Also, it was driving me crazy that the code would work in Excel 2010 and not in 2016.

I actually emailed one of my friends about this issue, and he returned with
Code: 
<code>wksht.Application.Goto(wksht.Range("A1"), True)</code>
. This actually ended up working, but I'm still perplexed as to why Range.Select suddenly stopped working in 2016.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Macro Runs Fine Until I turn On add-in Power Query
Replies
2
Views
1K
BennyBB
B
N
  • Question Question
Powerpivot for Excel 2010 Error Msg Inputting Formula
Replies
0
Views
936
numberpro11
N
gino59
  • Question Question
PowerPivot issue when trying to enter DAX formula
Replies
0
Views
3K
gino59
gino59
S
  • Question Question
Frustrated: works on my PC but now others!
Replies
5
Views
2K
Andrew Poulsom
Andrew Poulsom
S
  • Question Question
PowerPivot unhandled exception error only when double-clicking Excel file
Replies
0
Views
3K
salsgang
S

Forum statistics

Threads
1,226,840
Messages
6,193,277
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top