Run Time Errors Select method of Range Class Failed - Differing Locations

This is strange.... The code below was originally fired from a form control button on a tab called "Instructions and Initialization" and the code was embedded in a module in the excel sheet as Sub PullAccessData(). When I ran the code the first time after opening the spreadsheet it would run just fine. If I saved the results and closed the spreadsheet and then re-opened and repeated, no issues. When I pressed the form control button a second time (i.e. did not close and reopen the spreadsheet) the code would fail at location #1 in the code. This was annoying and I thought something within the code was staying active which was causing the sub to fail the second time around.

I decided I would try to disable the button at the end of the subroutine so the user could not fire a second time. I would first put another button on the same sheet which would re-enable the button and then stop all VBA macros. I figured this would clear whatever was staying active causing the problem (allegedly..)

I was having issues enabling and disabling the buttons so I changed the button to an ActiveX button and moved the code below to be attached that activeX button. When I do that it fails every time at location #2. I have tried starting and stopping the code at various locations to fine tune what may be causing the issues, but the only thing I have been able to determine is it is failing on range calls using the format of range.(Cells(j, 1),Cells(jEnd,2).Select

I am dumbfounded.

Please Login or Register  to view this content.

Still having issues but narrowing in I think...

I dimmed out the section where the subroutine connects to the databases via an ADODB.connection and then run multiple times without issue. The strange part is the location #1 in the code above where it was giving the run time error, when the code is broken to debug, the rows selected by the range statement have been deleted and yet the code is failing on this line.....

The act of closing and opening the spreadsheet must be resetting the activeX database connections so they dont cause an issue the first time the routine is run. But in my code I put lines to close the connections and set them to nothing, but that is not enough or I am going down the wrong path...

Originally Posted by protonLeah

Some comments:
1. The alphabet variables make code hard to follow; descriptive names make it more readable.
2. You have declared your c, i, l's, ... as Integer, yet you are assigning floating point values to them, e.g.: i = i#
note that variables referencing rows and columns should be Dim'd as LONG , not Integer (nor Single nor Double (both are decimal types))
3. You are calling a function "ValidData()" but you don't describe, for us, what it does or what it returns... Evidently, it only returns TRUE to the While loop.
4. Your variable, i, is incremented in the While loop, but as soon as you exit the loop, it is reset before being used.
5. Your line:

Sheets("DTT").Range(Sheets("DTT").Rows(j), Sheets("DTT").Rows(jend)).Delete xlShiftUp
should look like:
Sheets("DTT").Range(j & ":" & jend).Delete xlShiftUp

6. In: Range(Cells(j, 1), Cells(jEnd, 46#)).Select

46# is a FLOAT (i.e. decimal) data type, it must be a Long type

Thanks for the response.

Responses to your items noted above:
1. Point taken for posting code to the forum... my regular way of coding counters is to use simple alphabet characters
2. Will change and re-test the code with these re-declared as long.
3. Validdata is looking at the cells in noted tab. As soon as it hits a space, null, or zero length string it has hit the end of the data stack in that tab. The i is a counter of the rows. The starting row is set before the while loop starts to avoid the headers and cells above the real data. As soon as the ValidData returns False, then the data stack end has been reached and the row counter set as iEnd is set. i is then reset to the first row so that I have two variable i and iEnd which gives me the total rows of valid data with actual row numbers.
4. Answered in point 3 above.
5. I had the code written like that at one point, but then re-wrote it to be as explicit as possible to see if that was the issue.
6. Will change the row identifer as you have noted and re-test.

This has now been solved.

I was able to trace the problem down to the fact it was the shear volume of information being deleted by the rows of code was somehow throwing the clipboard for a spin. When the rows were being deleted it was deleting approximately 1600 columns of data. What led me to this conclusion was I used this exact same code on a smaller size spreadsheet and it worked fine everytime.

I then used the Sheets("X").usedrange.clear method to clear the data on the original spreadsheet and not only did it solve the problems, but it makes the routine a whole lot faster. Unfortunately some headers and formulas go by the wayside, but I just copy back a copy of the headers from another tab created just for that purpose.