Clever Use of End(xlDown) Needed

Dexter

Board Regular
Joined
Apr 3, 2002
Messages
71
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

Board Regular
Joined
Apr 3, 2002
Messages
71
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, forgot about going up a row:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,027
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top