resize range

  • Thread starter Thread starter Norma
  • Start date Start date
N

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
 
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)
 
Thank you so much, it works!!!!!!

Chip Pearson said:
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)
Click to expand...
 
Back
Top