Clever Use of End(xlDown) Needed

[Dexter]

You guys and gals on this site are brilliant and I appreciate the help I've received over the year.

Can you give me the syntax for selecting a range of unknown number of rows and a known number of columns. I would like to use the End(xlDown) code to first select the entire number of rows in a table, but would then like to "pull back" one row so as to exclude the last row. Then I would like to finish by selecting 4 columns to the right using an "Offset" command.

Any help would be appreciated.

 

[Andrew Poulsom]

Like this?

Range("A1:A" & Range("A1").End(xlDown).Row).Offset(0, 4).Select

 

[stevebausch]

Dexter,

Will attempt to help you out with this. My home town was Valley Park, is that a problem?

Usually, the guys here will propose using .End with xlUp; start from the bottom of the sheet and work upwards. The assumption is that the bottom is empty, and the table just might have a blank line or two.

If you are convinced your table will never have a blank line, then go ahead and start at the top.

To avoid screen flicker, besides using application.screenupdating = False, you don't have to .End (xldown).Select

You could set a variable to .End(xlDown).Row

Okay, I need to go back to work. And, I can't recall the rest of your query.

 

[Dexter]

Andrew and Steve,

Thanks for the replies. With your code, I am almost there. The last piece of the puzzle: once the end(xlDown) fires, I need it to then pull back one row such that I have selected the entire range of rows except for the last one.

And Steve, Valley Park is now a thriving metropolis with a brand new 6 lane Hwy 141 zipping right through it. I live near Big Bend and 141.

Dexter

 

[Andrew Poulsom]

Sorry, forgot about going up a row:

Range("A1:A" & Range("A1").End(xlDown).Row - 1).Offset(0, 4).Select