Resize CurrentRegion by 2 rows at the bottom
 

Simply want to select the CurrentRegion, excluding the first two title rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Resize CurrentRegion by 2 rows at the bottom
 

This will extend CurrentRange down by two rows:

Sub dural()
s = Split(ActiveCell.CurrentRegion.Address, "$")
s(UBound(s)) = s(UBound(s)) + 2
Range(Join(s, "$")).Select
End Sub

--
Gary''s Student - gsnu200836


"MikeF" wrote:


Simply want to select the CurrentRegion, excluding the first two title rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Resize CurrentRegion by 2 rows at the bottom
 

Does this line of code do what you want?

Selection.CurrentRegion.Offset(2).Select

--
Rick (MVP - Excel)


"MikeF" wrote in message
...

Simply want to select the CurrentRegion, excluding the first two title
rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Resize CurrentRegion by 2 rows at the bottom
 

Sorry, I used the active selection instead of A1 that your code indicated
you wanted to use. Simply replace Selection with your "starter" cell...

Range("A1").CurrentRegion.Offset(2).Select

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Does this line of code do what you want?

Selection.CurrentRegion.Offset(2).Select

--
Rick (MVP - Excel)


"MikeF" wrote in message
...

Simply want to select the CurrentRegion, excluding the first two title
rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Resize CurrentRegion by 2 rows at the bottom
 

On Wed, 4 Mar 2009 05:55:00 -0800, MikeF
wrote:


Simply want to select the CurrentRegion, excluding the first two title rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Here's an example with some debug.print commands so you can see what's going
on.

If the initial region is $A$1:$C$7, after running this Sub you should see
$A$3:$C$7



==================
Option Explicit
Sub RemoveTitles()
Dim c As Range
Set c = Range("A1").CurrentRegion
Debug.Print c.Address
Set c = c.Resize(c.Rows.Count - 2).Offset(2)
Debug.Print c.Address
End Sub
=========================
--ron

Resize CurrentRegion by 2 rows at the bottom
 

I see from your posting that I misinterpreted what the OP was looking for.
As you may remember from other postings of mine, I have this "thing" for
one-liners; so, while not a practical solution to the OP's question (I would
do it the way you posted), I thought those reading this thread might find
this one-liner of interest anyway...

Range("A3:" & Split(Range("A1").CurrentRegion.Address, ":")(1)).Select

It selects the same range your code would (if you had specified Select
instead of Address on your last line of code). HOWEVER, the code requires
that at least one line of data exists (or else the 2nd header row would be
selected).

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Wed, 4 Mar 2009 05:55:00 -0800, MikeF
wrote:


Simply want to select the CurrentRegion, excluding the first two title
rows,
including whatever number of columns there are.

Have tried a few dozen syntaxes, here's the latest that doesn't work...

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Select
Selection.Resize(Rows - 2,).Select


If anyone can help it would be greatly appreciated.
- Mike

Here's an example with some debug.print commands so you can see what's
going
on.

If the initial region is $A$1:$C$7, after running this Sub you should see
$A$3:$C$7



==================
Option Explicit
Sub RemoveTitles()
Dim c As Range
Set c = Range("A1").CurrentRegion
Debug.Print c.Address
Set c = c.Resize(c.Rows.Count - 2).Offset(2)
Debug.Print c.Address
End Sub
=========================
--ron

Resize CurrentRegion by 2 rows at the bottom
 

On Wed, 4 Mar 2009 12:54:52 -0500, "Rick Rothstein"
wrote:

I see from your posting that I misinterpreted what the OP was looking for.
As you may remember from other postings of mine, I have this "thing" for
one-liners; so, while not a practical solution to the OP's question (I would
do it the way you posted), I thought those reading this thread might find
this one-liner of interest anyway...

Range("A3:" & Split(Range("A1").CurrentRegion.Address, ":")(1)).Select

It selects the same range your code would (if you had specified Select
instead of Address on your last line of code). HOWEVER, the code requires
that at least one line of data exists (or else the 2nd header row would be
selected).

What I noted about your previous posting was that it only moved the current
region down two rows, but didn't resize it.

You can combine my two lines into one (obviously excluding the debug.print
lines), but I chose not too to make it easier for the OP to follow the logic.
--ron