Fully Qualify Range Utilizing Union

Brand new to the forum, not a programmer...but a pretty good VBA hack for a number of years.

I am working on a project and have come to a part that has me stumped. Using the Union function, I can't seem to get a fully qualified sheet range. If the sheet is active, the code works fine...but if I run it from a different sheet it fails with "Select Method of Range Class Failed" error is 1004 of course.

Here's the code, it builds the address range fine and I can view the addresses no problem with debug...but of course they are simply like this (this is the actual debug.print output that is in the code below:

$A$5561,$A$2774:$A$2776,$A$2767:$A$2771,$A$2761:$A$2762,$A$2759,$A$2753:$A$2754,$A$2748:$A$2750,$A$2731:$A$2737,$A$2727:$A$2729,$A$2724,$A$2722,$A$2717:$A$2719,$A$2715

No sheet reference. The sheet I am referencing here is called "Movies" by codename... When I run the code from a different sheet it wants to use the active sheet, which is protected, so it fails. If I precede the SELECT line (which will eventually be a DELETE if I get it working right) by "Movies.Activate" then it works fine, but again...not want I really want.

The variables are dim in the beginning of the code and the arrSplitRowsFound array is working as it should. I am using the .Cells because I have variables that fill in the Row reference...I left them out of here for simplicity sake, but replaced them with the actual values.

Been beating my head on this one, it took me a long time to figure out why it was failing...but now that I have proven it is the active sheet issue, I don't know how to fix it.

Any help would be fantastic.

Please Login or Register  to view this content.

Thanks.

Eric

Originally Posted by WasWodge

What does

Please Login or Register  to view this content.

give you?

and what happens with

Please Login or Register  to view this content.

If when you are stating "Select" you are literally doing

Please Login or Register  to view this content.

then you can't select a cell (or range of cells) on a sheet that isn't active.

Output from your first question is:

'[Movie List1.xlsm]Movies'!A5561,A2774:A2776,A2767:A2771,A2761:A2762,A2759,A2753:A2754,A2748:A2750,A2731:A2737,A2727:A2729,A2724,A2722,A2717:A2719,A2715

Using Application.Goto rngDeleteRows works...no errors.

Man...I feel like a dope! I am actually wanting to delete the rows anyway, not select them. I was just using select for testing so I make sure it was picking the right rows. Now I see that rngDelete.Rows.Entirerow.delete works without issue, which is what I was after anyway! When I tried that though, I got an error because I had a "Movies.Range("A4").Activate" in there and I assume you can't do that either without first activating the sheet....I did not realize it was in there and just assumed same error as before.

Is the output from your first question above correct? I would have expected all the ranges to have the full qualified sheet name...or, since you can't have more than one sheet in a range, it simply defaults the rest of the range to the first sheet qualifier...which seems to makes sense.

Thanks for the help on this, greatly appreciated!

Eric

Originally Posted by AlphaFrog

What .Select line? You don't show any .Select line of code. You can't .Select a range unless the sheet the range is on is active.

You probably don't need to .Select a range, but with the limited code provided, I don't know what you're doing.

You could .Delete a range without the sheet being active.

Sorry about that, I thought I provided the select code...it was just rngDeleteRows.EntireRow.Select

You are 100% right...I don't need the select and I can delete without it being active. Basically I have been on a wild goose chase for hours on end...

Thanks for the help. I appreciate it.

Originally Posted by WasWodge

Deleted as seeing the use of entirerow probably does mean Delete rather than clearcontents

I don't understand what you mean?

Originally Posted by WasWodge

When you posted originally it seemed strange to delete because it was a noncontiguous range but with the entirerow it makes sense and so my question was irrelevant which is why I deleted it.

Oh, I understand. I didn't see your post before you deleted it. Yes, entire row so deleting from the bottom up maintains the row ids and works fairly quick.

Thanks.