CurrentRegion less one Row

[GregR]

In code if you are using: set rng = activecell.usedregion, how do you
reduce the usedregion by one row. Would it be usedregion - 1? As an
example, the usedregion is A5:G10 and you want to set the range to
A6:G10. Would it be:

Set rng = activecell.usedregion - 1

TIA

Greg

[Norman Jones]

Hi Greg,

Try:

Set rng = ActiveCell.CurrentRegion

Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)


---
Regards,
Norman



"GregR" <gregrivet@gmail.com> wrote in message
news:1131149582.307076.93610@g44g2000cwa.googlegroups.com...
> In code if you are using: set rng = activecell.usedregion, how do you
> reduce the usedregion by one row. Would it be usedregion - 1? As an
> example, the usedregion is A5:G10 and you want to set the range to
> A6:G10. Would it be:
>
> Set rng = activecell.usedregion - 1
>
> TIA
>
> Greg
>

[Walt]

Hi Greg,

This is probably what you're after:

Sub ShortCurRegion()
Dim Rng As Range
With ActiveCell.CurrentRegion
Set Rng = .Cells(1, 1).Offset(1, 0). _
Resize(.Rows.Count - 1, .Columns.Count)
End With
Rng.Select
Set Rng = Nothing
End Sub

Best Regards,
Walt Weber

[Jim May]

Norman,
Does the Offset(1) below mean to move down 1 row, no columns?
Tks,
Jim

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23q6NbLa4FHA.2432@TK2MSFTNGP10.phx.gbl...
> Hi Greg,
>
> Try:
>
> Set rng = ActiveCell.CurrentRegion
>
> Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
>
>
> ---
> Regards,
> Norman
>
>
>
> "GregR" <gregrivet@gmail.com> wrote in message
> news:1131149582.307076.93610@g44g2000cwa.googlegroups.com...
>> In code if you are using: set rng = activecell.usedregion, how do you
>> reduce the usedregion by one row. Would it be usedregion - 1? As an
>> example, the usedregion is A5:G10 and you want to set the range to
>> A6:G10. Would it be:
>>
>> Set rng = activecell.usedregion - 1
>>
>> TIA
>>
>> Greg
>>
>
>

[Norman Jones]

Hi Jim,

Yes. Offset(1) is an abbreviation for:

Offset(1,0)

i.e. 1 row, 0 columns.


---
Regards,
Norman


"Jim May" <jmay@cox.net> wrote in message
news:%Y%af.7617$wC.781@dukeread06...
> Norman,
> Does the Offset(1) below mean to move down 1 row, no columns?
> Tks,
> Jim

[Jim May]

much obliged;
Jim

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23H17VYf4FHA.1416@TK2MSFTNGP09.phx.gbl...
> Hi Jim,
>
> Yes. Offset(1) is an abbreviation for:
>
> Offset(1,0)
>
> i.e. 1 row, 0 columns.
>
>
> ---
> Regards,
> Norman
>
>
> "Jim May" <jmay@cox.net> wrote in message
> news:%Y%af.7617$wC.781@dukeread06...
>> Norman,
>> Does the Offset(1) below mean to move down 1 row, no columns?
>> Tks,
>> Jim
>
>

[Tom Ogilvy]

Jim,

in the VBE, make the immediate window visible. (under view, select
immediate window)

in the immediate window, you can test expressions such as this

? range("A1").address
$A$1
? range("A1").Offset(1).Address
$A$2

finished each line with return

You can also work with your worksheet

Range("A1").Value = 29

for i = 1 to 10 : Cells(i,1).Value = i : Next i

Activesheet.Name = "BBB"

It is excellent for testing out complex string concatenations.

Just a suggestion if you don't know about it.

--
Regards,
Tom Ogilvy

"Jim May" <jmay@cox.net> wrote in message
news:tc0bf.7618$wC.203@dukeread06...
> much obliged;
> Jim
>
> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
> news:%23H17VYf4FHA.1416@TK2MSFTNGP09.phx.gbl...
> > Hi Jim,
> >
> > Yes. Offset(1) is an abbreviation for:
> >
> > Offset(1,0)
> >
> > i.e. 1 row, 0 columns.
> >
> >
> > ---
> > Regards,
> > Norman
> >
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:%Y%af.7617$wC.781@dukeread06...
> >> Norman,
> >> Does the Offset(1) below mean to move down 1 row, no columns?
> >> Tks,
> >> Jim
> >
> >
>
>

[Jim May]

Thanks Tom:
This will be my next assignment (after lunch).
This looks great.
Jim

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23IWeBNh4FHA.268@TK2MSFTNGP10.phx.gbl...
> Jim,
>
> in the VBE, make the immediate window visible. (under view, select
> immediate window)
>
> in the immediate window, you can test expressions such as this
>
> ? range("A1").address
> $A$1
> ? range("A1").Offset(1).Address
> $A$2
>
> finished each line with return
>
> You can also work with your worksheet
>
> Range("A1").Value = 29
>
> for i = 1 to 10 : Cells(i,1).Value = i : Next i
>
> Activesheet.Name = "BBB"
>
> It is excellent for testing out complex string concatenations.
>
> Just a suggestion if you don't know about it.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim May" <jmay@cox.net> wrote in message
> news:tc0bf.7618$wC.203@dukeread06...
>> much obliged;
>> Jim
>>
>> "Norman Jones" <normanjones@whereforartthou.com> wrote in message
>> news:%23H17VYf4FHA.1416@TK2MSFTNGP09.phx.gbl...
>> > Hi Jim,
>> >
>> > Yes. Offset(1) is an abbreviation for:
>> >
>> > Offset(1,0)
>> >
>> > i.e. 1 row, 0 columns.
>> >
>> >
>> > ---
>> > Regards,
>> > Norman
>> >
>> >
>> > "Jim May" <jmay@cox.net> wrote in message
>> > news:%Y%af.7617$wC.781@dukeread06...
>> >> Norman,
>> >> Does the Offset(1) below mean to move down 1 row, no columns?
>> >> Tks,
>> >> Jim
>> >
>> >
>>
>>
>
>

[GregR]

Norman, Tom and Walt, thank you.

Greg