Assume data entered from B4 (known point) to unknown Column (Y). Data will
be entered in many rows. Also assume that Col B is used to establish last
row used and that last row returns as (X). Would like to find and select
Range(B4:YX).

Have found and tried a couple of codes doing a search in the forumn that
seemed to address this but have not been able to get any of them tweaked.

I'm using 2003, if that matters. Hope this is clear enough. Thanks for any
help here.

Tryout ActiveSheet.UsedRange:

Play with this little peice of code:

Sub Macro3()
MsgBox (ActiveSheet.UsedRange.Address)
End Sub

If you have data in B2 thru D4 ( a small 3x3 block), then the macro will
return

$B$2:$D$4. You can capture the info as a string and then use it as a range
later:

dim s as string, r as range
s=ActiveSheet.UsedRange.Address
..
..
..
r=Range(s)
--
Gary's Student


"Don G" wrote:

Assume data entered from B4 (known point) to unknown Column (Y). Data will
be entered in many rows. Also assume that Col B is used to establish last
row used and that last row returns as (X). Would like to find and select
Range(B4:YX).

Have found and tried a couple of codes doing a search in the forumn that
seemed to address this but have not been able to get any of them tweaked.

I'm using 2003, if that matters. Hope this is clear enough. Thanks for any
help here.

Thanks for the quick reply and the direction.....playing with it now....will
let you know how it works out for me....

Don

"Gary''s Student" wrote:

Tryout ActiveSheet.UsedRange:

Play with this little peice of code:

Sub Macro3()
MsgBox (ActiveSheet.UsedRange.Address)
End Sub

If you have data in B2 thru D4 ( a small 3x3 block), then the macro will
return

$B$2:$D$4. You can capture the info as a string and then use it as a range
later:

dim s as string, r as range
s=ActiveSheet.UsedRange.Address
.
.
.
r=Range(s)
--
Gary's Student


"Don G" wrote:

Assume data entered from B4 (known point) to unknown Column (Y). Data will
be entered in many rows. Also assume that Col B is used to establish last
row used and that last row returns as (X). Would like to find and select
Range(B4:YX).

Have found and tried a couple of codes doing a search in the forumn that
seemed to address this but have not been able to get any of them tweaked.

I'm using 2003, if that matters. Hope this is clear enough. Thanks for any
help here.

OK Gary.....this is what I had to do to it...this much is working fine..but I
still have one problem with it:

Sub SelectRange()

'MsgBox (ActiveSheet.UsedRange.Address)

Dim s As String, r As Range
s = ActiveSheet.UsedRange.Address
Set r = Range(s)
r.Select

End Sub

I don't need the MsgBox popping up, so as you can see, I commented it out.
The macro also failed without the Set and Select entries. Works great as it
is above, but:

The problem is that there will be data above and to the left of the point
I'd like to start the range from. (Titles Above, Company Names to the left)
Using your example, assume there is data in A1.. The way it is now, A1 would
be picked up and not B4, the point where I want the Range to start. B4 is a
known and would be coded directly into the macro. Again, using your example,
I need to determine or extricate the $D$4 from the UsedRange function.

One work around I've thought about is to let the macro select the Range
first and then call the Titles and Company Names after that procedure was
completed. (These are called from a data entry sheet using formulas at the
present time) Not sure if that's the right way to go or not.

Again, thanks for your help here.

Don

"Gary''s Student" wrote:

Tryout ActiveSheet.UsedRange:

Play with this little peice of code:

Sub Macro3()
MsgBox (ActiveSheet.UsedRange.Address)
End Sub

If you have data in B2 thru D4 ( a small 3x3 block), then the macro will
return

$B$2:$D$4. You can capture the info as a string and then use it as a range
later:

dim s as string, r as range
s=ActiveSheet.UsedRange.Address
.
.
.
r=Range(s)
--
Gary's Student


"Don G" wrote:

Assume data entered from B4 (known point) to unknown Column (Y). Data will
be entered in many rows. Also assume that Col B is used to establish last
row used and that last row returns as (X). Would like to find and select
Range(B4:YX).

Have found and tried a couple of codes doing a search in the forumn that
seemed to address this but have not been able to get any of them tweaked.

I'm using 2003, if that matters. Hope this is clear enough. Thanks for any
help here.

O.K. then. Let's approach this step-wise:

1. move down column B to find the last filled cell in that column
2. move across the row to the last filled cell in that row

Range("B4").End(xldown).Select should get that last used cell in B
Selection.End(xlToRight).Select should get you to the end of you block of
cells


finally (although you don't need it) MSGBOX(Selection.Address) will test it
--
Gary''s Student


"Don G" wrote:

OK Gary.....this is what I had to do to it...this much is working fine..but I
still have one problem with it:

Sub SelectRange()

'MsgBox (ActiveSheet.UsedRange.Address)

Dim s As String, r As Range
s = ActiveSheet.UsedRange.Address
Set r = Range(s)
r.Select

End Sub

I don't need the MsgBox popping up, so as you can see, I commented it out.
The macro also failed without the Set and Select entries. Works great as it
is above, but:

The problem is that there will be data above and to the left of the point
I'd like to start the range from. (Titles Above, Company Names to the left)
Using your example, assume there is data in A1.. The way it is now, A1 would
be picked up and not B4, the point where I want the Range to start. B4 is a
known and would be coded directly into the macro. Again, using your example,
I need to determine or extricate the $D$4 from the UsedRange function.

One work around I've thought about is to let the macro select the Range
first and then call the Titles and Company Names after that procedure was
completed. (These are called from a data entry sheet using formulas at the
present time) Not sure if that's the right way to go or not.

Again, thanks for your help here.

Don

"Gary''s Student" wrote:

Tryout ActiveSheet.UsedRange:

Play with this little peice of code:

Sub Macro3()
MsgBox (ActiveSheet.UsedRange.Address)
End Sub

If you have data in B2 thru D4 ( a small 3x3 block), then the macro will
return

$B$2:$D$4. You can capture the info as a string and then use it as a range
later:

dim s as string, r as range
s=ActiveSheet.UsedRange.Address
.
.
.
r=Range(s)
--
Gary's Student


"Don G" wrote:

Assume data entered from B4 (known point) to unknown Column (Y). Data will
be entered in many rows. Also assume that Col B is used to establish last
row used and that last row returns as (X). Would like to find and select
Range(B4:YX).

Have found and tried a couple of codes doing a search in the forumn that
seemed to address this but have not been able to get any of them tweaked.

I'm using 2003, if that matters. Hope this is clear enough. Thanks for any
help here.