Creating a Changing Range

[James]

Hi everyone, Maybe a quick question. How do I create a range that knows
exactly how big the data range is? I Want the range to start at "B8" and go
to Last used row, and last used column. Is this possible? Thanks!

 

[JLGWhiz]

myRange = ActiveSheet.Range("B8").CurrentRegion
MsgBox myRange.Address

Stick this in a sub and see if it works.

 

[Tim]

you could try a 'dynamic named range' and refer to that in your
code/worksheet: -

http://www.ozgrid.com/Excel/DynamicRanges.htm

you just need to be careful about which rows/columns you choose to base the
range on (ie, they have to be ones that will always have an entry, otherwise
the 'shape' of the range will be wrong).

hth,

Tim

 

[James]

Thanks for the link, Tim. I tried this but cant seem to refrence it in my
code. i did a simple MsgBox MyRange.Address and it says 'object required' The
proper range is being selected when I do Insert->Name->Range. Is there a
special way to refrence it in the code. Couldnt find it on the page you gave
me. thx

JLGWhiz, I tried your version and it would work great but it selects
Range("A1:T373"), I want it to select only Range("B8:T373"). Could I do
something like

Set Rng = ActiveCell.CurrentRegion
Set MyRange=Rng.Offset(8,1).Resize(Rng.Rows.Count-8,Rng.Columns.Count-1)
MsgBox MyRange.Address

when i try this i get a "1004 application or object defined error"

=====================================================

 

[JLGWhiz]

OK James, I believe this will do what you want.

Sub getVarRng()
Rng = ActiveSheet.UsedRange.Address
Rng1 = Right(Rng, Len(Rng) - InStr(Rng, ":"))
Set myRange = Range("B8:" & Rng1)
MsgBox myRange.Address
End Sub

 

[JLGWhiz]

Just to be neat about it.

Sub gj()
Dim Rng As String, Rng1 As String, myRange As Range

Rng = ActiveSheet.UsedRange.Address
Rng1 = Right(Rng, Len(Rng) - InStr(Rng, ":"))
Set myRange = Range("B8:" & Rng1)
MsgBox myRange.Address
End Sub

In case you use Option Explicit.