CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

CurrentRegion less one Row
 

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

"Tom Ogilvy" wrote in message
...
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

CurrentRegion less one Row
 

Norman, Tom and Walt, thank you.

Greg