Redefine a Range

[Gary''s Student]

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

Thanks in Advance

 

[Rick Rothstein]

There is always this way...

Set R = Range("D31257")
Set R = R.Offset(1).Resize(R.Rows.Count-1)

 

[Rick Rothstein]

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

Set R = Range("D31257")
Set R = Range(R(2), R(R.Count))

 

[John_John]

Hi!
One way is below:

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

...but is not the only one!

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

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200908

Hi!
One way is below:

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

..but is not the only one!

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

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200908

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

Set R = Range("D31257")
Set R = Range(R(2), R(R.Count))

--
Rick (MVP - Excel)




.

 

[Rick Rothstein]

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

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

 

[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("D31257")
Set R = Intersect(R, R.Offset(1))

 

[Peter T]

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

 

[John_John]

One more way:

With Range("D31257")
.Cut .Offset(1)
End With

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

 

[Rick Rothstein]

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.