Applying restrictions around ActiveSheet.Rows(Target.Row).Select

[Beam_me_up]

Hi,

I'm sure (or at least hoping) this is an easy one, but here goes.

I have a worksheet where I need an entire row to be selected whenever the user clicks any cell for that row. This is achieved using:

Please Login or Register  to view this content.

There are 2 issues with this:

1. I want this to be confined only to rows in the worksheet where this is data. For example rows 1-7 are blank, and row 8 the header so these should be ignored. Equally any rows after the last row of data (according to what's in column A - which will always have values) should also be ignored. The number of rows with data is variable. Can the code be amended in someway?
2. The worksheet in question also includes buttons assigned to 'sort' macros enabling the data to be organised according to a number of different criteria (first name, surname, DOB, etc). However the above procedure prevents these from working due to the requirement of selecting a range of cells to sort. Is there a way of "turning off" the procedure to enable the sort to take place after any of the buttons are clicked, and then it "turnt on" again afterwards?

Help would be very much appreciated!

Beam

[Beam_me_up]

Ok, I've sussed no.2 by disabling events before the sort and reenabling afterwards. Can anyone help with 1?

[HaHoBe]

Hi, Beam,

like this

Please Login or Register  to view this content.

Ciao,
Holger

[HaHoBe]

Hi, Beam_me_up/Beam_me_ip,

please have a look at Rule #8 - you can find the corresponding rule at Mr. Excel as well.

http://www.mrexcel.com/forum/excel-q...ow-select.html

Holger

[Winon]

@ HaHoBe,

Nice catch Holger!

[mikerickson]

Something like

Please Login or Register  to view this content.