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

Hi Greg,

Try:

Set rng = ActiveCell.CurrentRegion

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


---
Regards,
Norman



"GregR" wrote in message
ups.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,
Does the Offset(1) below mean to move down 1 row, no columns?
Tks,
Jim

"Norman Jones" wrote in message
...
Hi Greg,

Try:

Set rng = ActiveCell.CurrentRegion

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


---
Regards,
Norman



"GregR" wrote in message
ups.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

Hi Jim,

Yes. Offset(1) is an abbreviation for:

Offset(1,0)

i.e. 1 row, 0 columns.


---
Regards,
Norman


"Jim May" 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

much obliged;
Jim

"Norman Jones" wrote in message
...
Hi Jim,

Yes. Offset(1) is an abbreviation for:

Offset(1,0)

i.e. 1 row, 0 columns.


---
Regards,
Norman


"Jim May" 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,

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" wrote in message
news:tc0bf.7618$wC.203@dukeread06...
much obliged;
Jim

"Norman Jones" wrote in message
...
Hi Jim,

Yes. Offset(1) is an abbreviation for:

Offset(1,0)

i.e. 1 row, 0 columns.


---
Regards,
Norman


"Jim May" 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

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

Norman, Tom and Walt, thank you.

Greg