Resize CurrentRegion by 2 rows at the bottom

[MikeF]

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

 

[Gary''s Student]

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

 

[Rick Rothstein]

Does this line of code do what you want?

Selection.CurrentRegion.Offset(2).Select

 

[Rick Rothstein]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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).

 

[Ron Rosenfeld]

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