Dynamically defining a range in VBA?

[jross20]

After doing some googling I'm not sure if the way I am wording this is correct or not... basically I have a sheet in which I'd like to run a sub on... but I'd like to have it auto set the range based on some logic like (if cell such and such is not blank, include in range). Seems like when I think "dynamically" this is not what others are thinking.

i.e. Instead of a sub that runs on say A1:A1000, I'd like to be able to dynamically change the Range value (A1:A1000) based on whether or not the cells are filled. So, if only A1 through A6 are filled, run the sub on A:1:A6. I was hoping this might increase performance to only run the heavier function on fewer cells.

 

[Joe4]

Yep, I do this all the time.

You can find the last row in column A with data like this:

Dim lr as Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

Then you can dynamically build your range, like this:

Dim rng as Range
Set rng = Range("A1:A" & lr)

 

[jross20]

Yep, I do this all the time.

You can find the last row in column A with data like this:

Dim lr as Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

Then you can dynamically build your range, like this:

Dim rng as Range
Set rng = Range("A1:A" & lr)

Okay so I am going to read this back to make sure I am understanding it and learning it correctly.

So, we create a a dim called LR and change the type to a numeric one. We then define LR as being the count of Rows in column A that... Are not empty? The End(xlUp) is new to me but I presume it has something to do with that? Then we define out dim range by using the first dim to create the row number for the end of the range line.

That is actually much smaller than I expected!

 

[Joe4]

"lr" and "rng" are just variables. You use "Dim" statements to declare what type of variables they will be.
This is not necessary, but by doing that, you reduce the possibility of errors (i.e. prevent text entries going into numeric variables).
See here for more on variables in VBA: Declaring variables (VBA)

Rows.Count simply returns the last possible row on your worksheet. For newer version of Excel, this is row 1048576. Old versions of Excel only had 65536.

Cells(Row, Column) is sinply a way to refer to a range. The Column reference can be the column letter or the column index number.
So these three range reference all refer to the same cell (cell "D19"):
Range("D19")
Cells(19, "D")
Cells(19, 4)

So Cells(Rows.Count, "A") is putting us down at cell "A1048576".
.end(xlUp) is the same thing as hitting CTRL + the up arrow. This finds the last cell in column A with data in it.

Does that all make sense?