Select column in a list

Posted by Matt P on March 14, 2001 8:45 AM

Suppose you have titles in Row 1 and data in Column A, Rows 2-6 (sample below). Is there code out there that, if you had the cursor in B2 could select cells B2:B6 (i.e. recognizing that the list's end is in row 6)?

Code Translation
011
122
233
344
455

The list I receive has a different number of rows each time. Thus, I now add a formula in B2 that converts the code to words, and must copy down to the end of the list. With 6000 rows, this becomes cumbersome. So, I would like my macro to select B2, set the range to the last row in the region and copy down. Is this possible?
Thanks in advance!

Posted by Mark W. on March 14, 2001 8:56 AM

Matt, Excel already supports this functionality.
You can enter a formula in B2 and then double-
click the fill "handle" -- that little black
square in the lower, right-hand corner of the
selected cell.

Posted by Dave Hawley on March 14, 2001 8:59 AM

Hi Matt

You could use this

Sub TryThis()
Range("B2", Range("B65536").End(xlUp)).Select
End Sub

Dave
OzGrid Business Applications

Posted by Dave Hawley on March 14, 2001 9:06 AM

Matt, here this entire code in case you need it.

Sub TryThis()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("B2", Range("B65536").End(xlUp)).AutoFill _
Destination:=Range("B2:B" & LastRow)
End Sub

Dave

OzGrid Business Applications