Dynamic Range Between Dual Offset

[Phoenix5794]

I'd like to know if it's possible to set a dynamic range based on a count between two offset cells.

I have a table that is being copied from another sheet and pasted into my summary sheet (inserted between data) which I'm able to do fine, it's the matter of deleting anything in this range (such as an old table that was left from before) that I'm having a hard time with.

This is a combination of VBA and Excel functions that I'm using:

Please Login or Register  to view this content.

Where the range DeleteHASummary is my Excel defined range that I've set to something like this:

=OFFSET(HASummary,3,-3,COUNTA(OFFSET(HASummary,3,-3):OFFSET(SummaryEnd,-1,0))

So the text in bold is made up of course, but that's the gist of the range that I'm trying to count through. I could set that range that I'd like to count, but when my macro deletes the range, it returns a #REF error (as it should since all the cells in the range are gone).

If I could do this all in VBA, I'd be glad to, but I couldn't figure out how to utilize the Application.Union method with offsets (plus I need to select the entire row as I need to delete all the information).

Hope you're able to figure out what I'm trying to do from what I've posted here, if not, I'll try to get a working example going.

Thanks!

***********************

[SOLVED]

Named Range (DeleteHASummary):
=OFFSET(HASummary,3,-3,COUNTA(OFFSET(HASummary,3,-3):OFFSET(ColdAirManifoldSize,-2,-3)))

[Phoenix5794]

Would this be easier with VBA or am I right in attempting to base it on some VBA code along with more complicated dynamically named ranges?

[Phoenix5794]

Ok, nevermind..
The Excel function equation I thought was totally bogus actually works.

Named Range (DeleteHASummary):
=OFFSET(HASummary,3,-3,COUNTA(OFFSET(HASummary,3,-3):OFFSET(ColdAirManifoldSize,-2,-3)))

I didn't think I'd be able to count down a range based on an offset cell which is delimited by two additional offset ranges (seemed absurd to me).

It works though, so hopefully this will at least prevent someone else from not trusting themselves.