| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| Redefine UsedRange property of Worksheet Object | Excel Programming | |||
| Redefine range of cells, array formula returns NA | Excel Discussion (Misc queries) | |||
| links on network needs redefine path whenever open | Excel Programming | |||
| Redefine Table Array | New Users to Excel | |||
Hybrid Mode
