Expand Selected Range

I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.

The original range changes at various times. Some times it might be C7: C452 etc.

I know it's easy, but I can get the code to keep the selection.

Please Login or Register  to view this content.

Sorry, I guess that I did a poor job of explaining. I'll try again.

As an example: I have 7 rows of data, and this data will continue to grow. My macro currently selects C7 which is the top left cell of the data. I then go End Down with the macro, selecting all cells from and including C7 down to the last row of data, example C12. Now I want to keep this current selection and expand it 18 columns to the right. Now cells between C7 and U12 are selected.

Next week, the data may have 176 rows of data.

What I can't do is expand the selection over to include column U.

Thats as complete as I can describe it.

Try this, hopefully there will be something in Column U

Please Login or Register  to view this content.

hi all,

To make Dave's suggestion more flexible for versions of excel released since Excel 2003* I would change it to use the maximum number of rows. Also, I recommend explictly defining the worksheet (eg "blah") because errors may occur if the expected sheet is not active at the time the macro is run.

Please Login or Register  to view this content.

*I'd say this is very unlikely to occur in your current situation where you discuss "176 rows" as an example, but if you ever have a really massive data set or adapt this solution for other uses it may become more important.

hth
Rob

It's not clear if Column C is the only column with contiguous data (ie you can not use U to determine last row), if so then combine all approaches illustrated thus far:

Please Login or Register  to view this content.

so the above determines last row based on Col C content and the resizes the range to be 19 columns wide (along similar lines to Proton Leah's earlier post).

Good Morning . . .

I came up with the solution last night. I was too tired to post, so here it is. Column U does not always have data. Column C always have data.

Just to let you know, all the columns from C thru U have headers and each header has a link to a macro that sorts by that column. The 1st two rows of the following macro is the start of each header macro. It works great now.

Please Login or Register  to view this content.

I'll mark as SOLVED.