Unknown Range Size Selection in Excel VBA 1

[Lightning]

OK, Here's an easy one for Excel developers.

I need to consecutively select a number of different sized ranges in an excel spreadsheet in code. The catch is that I don't know how many cells will be in each range. All I know is the starting cell and the number of columns wide the range is.

What VBA code should I use to select the range? The only reference materials I have all contain examples using a known range (eg B1:F3), but not an unknown range. How do you do what is probably a very common task?

Second question - can anyone suggest some good VBA for Excel reference books/materials?

TIA

Lightning

 

[logius]

Well, for your first question, just use the

Offset

property of the

Range

object. For a range you want to select, it would go something like this:

Range("A1", Range("A1").Offset(0,3))

That will select the range A1:A4. If you want to join up multiple non-consecutive ranges, then use the

Union

function. To join up two non-consecutive ranges, use something like this.

Dim MyRange As Range
Set MyRange = Union(Range(A1:A9), Range(C4:H7))

You can even keep adding to it.

Set MyRange = Union(Range(B3:B8), MyRange)

As for your second question, I had no clue how to write Excel macros little more than a month ago. It's amazing how much you can learn just by visiting sites online (just search for "Excel VBA&quot not to mention browsing this forum. If you feel you would be better aided with a book, then most of the books by O'Reilly publishers are worthwhile. I actually read VB & VBA In a Nutshell but only found it useful half the time. Once you get comfortable, use the built in help files to learn more (click on the Union command and hit F1). ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[Lightning]

Thanks for your reply Logius, but I may have not explained my problem clearly.

With these blocks of cells, I don't know all the cell addresses, so I can't code in a set range. While the ranges will all be four cells wide, and I can deduce the starting cell, the ranges can be anything from one to twenty rows deep. I need to select all the rows without knowing how many there are. Since I don't know how many rows there are, I can't just use the Offset property. As there are approximately 6 of these ranges to a worksheet, with up to fifteen worksheets to a workbook, and about twenty workbooks to work through, you can see why I need some generic code.

My question is probably better expressed as "How do I code the use of the [SHIFT]-[END]-[ARROW] keys to select a range of unknown width and unknown depth?"

Thanks again in advance.

Lightning X-)

 

[logius]

Uhhhh... Not sure what you mean. If you want to select an entire column (or the rest of a column) you can use the xlDown property of the Range class.

Range(Range("A2"), _
      Range("A2").Offset(0, 3)).End(xlDown).Select

'or to select a column starting at A3
Range("A3", Range("A3").End(xlDown)).Select

This property will continue down until it hits a blank, selecting every cell as it goes. There's also

xlToRight, xlToLeft, xlUp

. Here's a good site to go to and read up on various Excel VBA items including ranges.

http://www.cpearson.com/excel/topic.htm

----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[Lightning]

Thanks Logius, It was the ".End(xlDown)" syntax that I needed. Thanks also for the link. That looks like it could be very helpful.