Redefine a Range
 

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

There is always this way...

Set R = Range("D31:D257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

Here is another method for you to choose from (which I kind of like for its
brevity)...

Set R = Range("D31:D257")
Set R = Range(R(2), R(R.Count))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
There is always this way...

Set R = Range("D31:D257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

Hi!
One way is below:

With rngCur
Set rngCur = .Rows(2 & ":" & .Cells.Count)
End With

...but is not the only one!

Ο χÏ�ήστης "Gary''s Student" Î*γγÏ�αψε:

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

Thanks!
--
Gary''s Student - gsnu200908


"John_John" wrote:

Hi!
One way is below:

With rngCur
Set rngCur = .Rows(2 & ":" & .Cells.Count)
End With

..but is not the only one!

Ο χÏ�ήστης "Gary''s Student" Î*γγÏ�αψε:

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

Thanks!
--
Gary''s Student - gsnu200908


"Rick Rothstein" wrote:

Here is another method for you to choose from (which I kind of like for its
brevity)...

Set R = Range("D31:D257")
Set R = Range(R(2), R(R.Count))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
There is always this way...

Set R = Range("D31:D257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908


.

Redefine a Range
 

You can shorten that slightly by leaving the Cells references out...

Set rngCur = .Rows(2 & ":" & .Count)

--
Rick (MVP - Excel)


"John_John" wrote in message
...
Hi!
One way is below:

With rngCur
Set rngCur = .Rows(2 & ":" & .Cells.Count)
End With

..but is not the only one!

Ο χÏ�ήστης "Gary''s Student" Î*γγÏ�αψε:

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

You are welcome. Just to add to the idea that "there is always more than one
way to skin a cat", here is yet another way to do it...

Set R = Range("D31:D257")
Set R = Intersect(R, R.Offset(1))

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Thanks!
--
Gary''s Student - gsnu200908


"Rick Rothstein" wrote:

Here is another method for you to choose from (which I kind of like for
its
brevity)...

Set R = Range("D31:D257")
Set R = Range(R(2), R(R.Count))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
There is always this way...

Set R = Range("D31:D257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908


.

Redefine a Range
 

normally you can, but not always, eg

Set rngCur = Columns(1)
With rngCur
Debug.Print .Rows(2 & ":" & .Count).Address
Debug.Print .Rows(2 & ":" & .Cells.Count).Address
End With

Regards,
Peter T

"Rick Rothstein" wrote in message
...
You can shorten that slightly by leaving the Cells references out...

Set rngCur = .Rows(2 & ":" & .Count)

--
Rick (MVP - Excel)


"John_John" wrote in message
...
Hi!
One way is below:

With rngCur
Set rngCur = .Rows(2 & ":" & .Cells.Count)
End With

..but is not the only one!

? ??????? "Gary''s Student" ???????:

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908

Redefine a Range
 

One more way:

With Range("D31:D257")
.Cut .Offset(1)
End With

Ο χÏ�ήστης "Rick Rothstein" Î*γγÏ�αψε:

You are welcome. Just to add to the idea that "there is always more than one
way to skin a cat", here is yet another way to do it...

Set R = Range("D31:D257")
Set R = Intersect(R, R.Offset(1))

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
Thanks!
--
Gary''s Student - gsnu200908


"Rick Rothstein" wrote:

Here is another method for you to choose from (which I kind of like for
its
brevity)...

Set R = Range("D31:D257")
Set R = Range(R(2), R(R.Count))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
There is always this way...

Set R = Range("D31:D257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908


.


.

Redefine a Range
 

I assumed the when Gary''s Student included this line as part of the
description...

"I am given a range which is part of a single column"

that he was ruling out the possibility of an entire column being selected.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
normally you can, but not always, eg

Set rngCur = Columns(1)
With rngCur
Debug.Print .Rows(2 & ":" & .Count).Address
Debug.Print .Rows(2 & ":" & .Cells.Count).Address
End With

Regards,
Peter T

"Rick Rothstein" wrote in message
...
You can shorten that slightly by leaving the Cells references out...

Set rngCur = .Rows(2 & ":" & .Count)

--
Rick (MVP - Excel)


"John_John" wrote in message
...
Hi!
One way is below:

With rngCur
Set rngCur = .Rows(2 & ":" & .Cells.Count)
End With

..but is not the only one!

? ??????? "Gary''s Student" ???????:

I am given a range which is part of a single column, say D31:D257
I need an easy way to clip off the top cell, leaving D32:D257

Thanks in Advance
--
Gary''s Student - gsnu200908