Help with Excel Ranges

Cauld

So I do this report every month and for whatever reason I want to automate it. I've automated most of my reports, but I'm stuck on this one part of one. I don't think this should be too hard, but I can't seem to figure it out.

I want to sort my data, to do this in VBA I need the range of the data, but this range will change ever time. I'll have the same number of columns, but a different number of rows. so, using cell notation I want my range variable, z, to be something like this (that works):

z = (cells(1,1), cells(8,x)

where x is the number of rows. I was planning on using the funky LastCell deal to get that, but I don't know how to integrate it into a range.

Or, if its easier, how do I define the current selection as a range in VBA. Because I can do this:

Cells(1, 1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

and that selects the range I want to define within my macro.

Daenris

Why not just select the whole column? I'm not sure off the top of my head how to do that with VBA, but as long as there's nothing else in those columns other than the data you're sorting you can select either the range of rows, or the entire column and the sort should be the same.

Cauld

that's a good point, so I'll do that. Thanks


But I'd still like the answer to my question, just on principle! :P

Pheezer

There's really not a "good" way to do it. The way I do it is the way they describe in the help documentation on Excel's VBA bits in a few spots.
Here it is:
Set rng = activesheet.range("A1:A65536")
rowCount = worksheetfunction.counta(rng)

If you declare rng the way you should, you need to declare it as a Range object. rowCount needs to be an Integer.
You're setting a range equal to the maximum possible length of your column, in this case the hard limit Excel places. Then you use the worksheet function CountA, which counts the number of non-blank cells in the range. Since the range is really a column, you're getting an accurate count on the number of populated rows. Since you're populating from the top down in all cases, you know that this is the row number of the last row in your list.

Yes, there are assumptions in there, but they're pretty safe assumptions in the cases where I use them. Make sure you use a column that won't have any blank values in the middle of your list. In areas where I might have blank rows, I use the special cells like you're doing there.

By the way, if you can use it in VBA to select the last cell when you're telling Excel to select cells, you can probably use it anywhere you need a cell reference. So you don't really need to define a range based on that, you can just use more or less what you used to make that selection in your code in your first post.

But if you're determined, there's an ActiveSelection range object, or something like that. I can't remember the actual name off hand, but there is something you can use to set rng = activeselection, and then you can pull the rng.rows.count value to get the end of the list. You might find that to be useful knowledge at some point later on.

Pheezer

oh, and remember that just about anything that ever counts as a Range object of any flavour has .Rows and .Columns and .Rows and .Columns always have .Count. You can probably just strip the .Rows.Count right out of the selection itself. This is useful because you CAN declare ranges using Range(Cells(1,1), Cells(1,4)). Columns, Rows. And you can substitute variables, and special variables and properties and anything else you can imagine that is a valid Integer into Cells(,).