Range selection using variable

[miles44]

I have a program which needs to select a ranges of cells, depending on the value in the variable.

Let's say the variable is worth 32, which would be the row number and the column is always D, with D32 being the first cell and D48 being the last.

What would the syntax be to select the range of D32 to D48?

Please Login or Register  to view this content.

I know this can't be right, so if you could help, I'd be most thankful

[Richard Buttrey]

Hi,

Please Login or Register  to view this content.

..but remember most of the time it's unnecessary to use .Select - in fact it just slows things down. If you want to use the range for say copying then use

Please Login or Register  to view this content.

[miles44]

Problem is, the end of the range will be 20 cells down from the top of the range. So, it will not always be D48.

The top could be 210 with the bottom cell being 230.

[Richard Buttrey]

Problem is, the end of the range will be 20 cells down from the top of the range. So, it will not always be D48.

The top could be 210 with the bottom cell being 230.

But that isn't what you said in post #1 when you said D48 was the last cell. And if the gap is 20 cells how does that square with D32:D48?

Maybe

Please Login or Register  to view this content.

[dflak]

I am bringing up a point which is a pet peeve of mine. On what sheet in the workbook would you like this to happen? It will happen on whatever sheet Excel thinks is active which is sometimes different than the sheet you think is active.

I recommend doing something like the following:

Please Login or Register  to view this content.

Always start your modules with Option Explicit. This forces you to declare your variables and if you go to Debug -> Compile Project it will tell you where you failed to declare a variable. Or the code will tell you when you try to run it. Without Option Explicit, you can misspell a variable name and not know it and you think it has a value but it is actually zero or null or false.

I like to use shortcuts for sheets for two reasons. The first is obvious: less typing. The second is a bit more subtle: when you assign a sheet, it knows what workbook it belongs to. This is important when the code has more than one workbook open.

Please note how I explicitly told Excel what sheet to go to for copy and what sheet to go to for paste.

Shown here is the line: NumRow = shC.Range("B2").

What this line does is go to cell B2 on the spreadsheet and read the value that is there. So if you are changing the starting line, you can just change the cell value and don't have to change the code.

As Richard mentioned, avoid using Select when you can. Generally speaking, if you record a macro and it produces code like:

Please Login or Register  to view this content.

You can replace it with

Please Login or Register  to view this content.

[miles44]

Richard: Thanks! That got it!
Sorry about the confusion.