Referring to an area relative to a named range

**petalred** · 09-07-2009, 04:10 PM

Hey there! Before I begin with my problem, let me go ahead and say that I can't post the sheet I'm working on or any part of it on here, because the work belongs to my employers and would constitute the loss of trade secrets. It would also take me way longer than I have the time to invest to put together a functional duplicate of the relevant part of the sheet just to be able to post it.

With that in mind, what I'm trying to do is refer to part of a range that spans five columns (though it could conceivably span more in the future, so I'd rather not count on that detail) and a constantly changing numbers of rows. The part I want to refer to includes all columns, and all rows except the first and the last in the range.

I know I can use the following:

Please Login or Register  to view this content.

to refer to everything in the range except the first row, but that doesn't help me with the last row, and in fact also includes the row after the last row. How would I shave those last two rows off of this, or otherwise refer to every part of a range other than its first and last rows?

In essence, I have a list and a button that allows people to add rows to that list (which always fall between the first and last rows, both of which are hidden from view and serve only as place holders to define the ever-expanding range). However, every time someone adds an item to that list, I want the list sorted alphabetically. The problem? It can't sort the invisible placeholder lines in with the rest of the list, but without those placeholder lines I have no way of defining the top and bottom of the list by way of the name "ConventionalWorksheet."

Does that make sense?

Any help would be much appreciated! Thanks!

**Leith Ross** · 09-07-2009, 04:19 PM

Hello petalred,

The object variable Rng now references all the cells in the range from the second row and to the row before the last row.

Please Login or Register  to view this content.

**petalred** · 09-07-2009, 04:31 PM

Thank you so much! That gave me just the information I needed! I have another question now, though. I don't have much experience doing this sort of programming, and I really only got stuck doing this for my company because I took it upon myself to learn how to make a better spreadsheet for my job than the one I inherited from my predecessor. So, a lot of what I've been doing has involved creating a macro and then dissecting it to learn how the processes work and how to adapt them for my own usage. In order to alphabetize the list of information, I'm currently using this code:

Please Login or Register  to view this content.

But that's pretty much verbatim the code generated by the macro (with the exception of the selected area which I've amended using your help). Could somebody help me decipher what all these things mean, so I can streamline the code and cut out the unnecessary stuff. I'm trying to keep my coding as clean as possible and this looks like it was written by a machine that didn't know any better (which it was).

Thanks again!

**petalred** · 09-07-2009, 04:38 PM

Actually, I also just noticed the section that says "Key:=Range("B9")" What will happen to this code if the first cell being alphabetized isn't B9? It may not always be.

**petalred** · 09-07-2009, 05:01 PM

I've now condensed that code into the following, using the help files:

Please Login or Register  to view this content.

And I think that's all I need, but I still want to replace "B9" with a cell relative to the range, rather than one explicitly named, so I need to understand the Key. I tried changing "B9" to "ConventionalWorksheet").Offset(1, 0).Resize(RowSize:=Range("ConventionalWorksheet").Rows.Count - 2 and it confused the .Apply parameter. Is the Key always just the top-left cell in the range to be sorted?

Thanks!