resize range

[Norma]

I'm having problems to resize a range.
my data looks like this

x a d
x a d
x a d
y b

when I select the data the last row is included. is there any way to resize
the selection and exclude the last row (y)

thank you

 

[Rick Rothstein]

When you select the data how... in code? If so, show us your code.

 

[Chip Pearson]

Try something like the following:

Dim Curr As Range
Dim R As Range
Set Curr = ActiveCell.CurrentRegion
With Curr
If .Rows.Count > 1 Then
Set R = .Resize(.Rows.Count - 1, .Columns.Count)
End If
End With
R.Select


The CurrentRegion Curr is the rectangular region containing the
ActiveCell that is bounded on the sides by either all blank cells or
the end of the worksheet. The range R will refer to the CurrentRegion
excluding the last row.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Norma]

Thank you so much, it works!!!!!!

Try something like the following:

Dim Curr As Range
Dim R As Range
Set Curr = ActiveCell.CurrentRegion
With Curr
If .Rows.Count > 1 Then
Set R = .Resize(.Rows.Count - 1, .Columns.Count)
End If
End With
R.Select


The CurrentRegion Curr is the rectangular region containing the
ActiveCell that is bounded on the sides by either all blank cells or
the end of the worksheet. The range R will refer to the CurrentRegion
excluding the last row.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)