function that finds length of a table; Cannot run on a different sheet.

[JimmyWilliams]

Hi guys,

The BlanksToSkip() code I use all the time and want to modify it, so it can work within the sub Sample().
I have highlighted where it trips up. In the past I have had to use "Active.worksheet" before calling BlanksToSkip(). I would prefer to have a way for BlanksToSkip to work regardless, or maybe another parameter as the worksheet? whatever is best.

Thanks!
Jimmy

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It seems to work when I comment out the 'X2.Select. I think. thanks AlphaFrog. I still need to check something else though.

Like there is another problem that's linked to this, but I am going to continue it on the related thread:
https://www.excelforum.com/excel-pro...ml#post4807023


...
I just tired this again, and I had to UNCOMMENT out the 'X2.Select to get it to work..
So I problem is a bit of a weird one.

[AlphaFrog]

You can't select a range unles its parent sheet is active. Try this...

Please Login or Register  to view this content.

Or this...

Please Login or Register  to view this content.

Alternatively, you can write your code so that it doesn't select any ranges. Selection of a range is not required in your case.

[Zer0Cool]

Selecting ranges has very few practical uses but many reasons not to use it. As mentioned, you cant select a cell on an inactive sheet. Typically you can act upon a range or other object without needing to select it and without needing to change sheets.

[JimmyWilliams]

Thanks Zer0,

Application.GoTo X2, seems to be working. for now..

[xlnitwit]

Out of curiosity, why are you using CallByName there?

[JimmyWilliams]

Out of curiosity, why are you using CallByName there?

I haven't a clue. its been +6month since I wrote this bit, still using it but haven't looked at it. I'm sure I had a reason back when I first made it. Probably the only thing I could get working.
If you know/think of something better, let me know!

Jimmy

[xlnitwit]

I can't see any reason not to simply use

Please Login or Register  to view this content.