Range Selection Code Help

[mattyp]

Hi Everyone, I have this selection code that works perfectly. It ensures all data in a column is selected (including blanks):

Please Login or Register  to view this content.

My problem comes when I want to have the Column select vary by using a string. I'm not sure how to incorporate it? I've tried with and without quotation marks with no luck. Anyone know how to do this?

Please Login or Register  to view this content.

[GeneralDisarray]

Hi Everyone, I have this selection code that works perfectly. It ensures all data in a column is selected (including blanks):

Do you mean, you are just trying to find a range that identifies the first cell to the last cell, in a given column?

I would suggest using something like this:

Please Login or Register  to view this content.

But, it's a very good practice to identify the sheet you want to look at (as below).

Please Login or Register  to view this content.

[mattyp]

Hi!! Sorry I forgot an important part of the code. "r" is the current rows letter value. My issue is I'm having trouble adding "r" to the line of code - the code below doesn't work no matter how I format it. I'm afraid I don't understand why either.

Please Login or Register  to view this content.

[GeneralDisarray]

Try using something like this:

Please Login or Register  to view this content.

[mattyp]

That worked perfect! Thank you General! Would you mind explaining how this script is able to select all the data? I've confirmed it works - I just don't fully understand.

[GeneralDisarray]

The key is using 'Resize'.


First, understand what this does: Cells(Rows.Count, "R").End(xlUp)

It's equivalent selecting cell R1048576 with your mouse, pressing the 'end' button on your keyboard, then pressing the up arrow. If you do that, you will see your selection travel up to the last non-blank cell in column R.

To find the row number, we just use .Row, so the code becomes Cells(Rows.Count, "R").End(xlUp).Row. But, you are starting in the second row (R2) - so we need to take 1 away from that number:


Finally, we need to pass this number back to Resize() to tell Excel "I want to start in cell R2 and 'go' down this many rows - Cells(Rows.Count, "R").End(xlUp).Row -1. Then I want to select that result".

[mattyp]

That explains it perfectly! Thank you so very much for your time and assistance. It really is greatly appreciated.

[GeneralDisarray]

Glad to be of help, please mark your thread 'solved'.

(from FAQ)

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved