Redefine a Range
in [Programming]
|
Prev: Run Code in Another Workbook
Next: Macro to populate cell value from another sheet based upon the data validation list selection
From: Gary''s Student on 11 Nov 2009 13:50 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" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message > news:uChQlYuYKHA.4012(a)TK2MSFTNGP04.phx.gbl... > > 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" <GarysStudent(a)discussions.microsoft.com> wrote in > > message news:EA56C9B1-0DE5-4203-A50E-2DE3EF79E51A(a)microsoft.com... > >>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 > > > > . >
From: Rick Rothstein on 11 Nov 2009 14:05 You can shorten that slightly by leaving the Cells references out... Set rngCur = .Rows(2 & ":" & .Count) -- Rick (MVP - Excel) "John_John" <JohnJohn(a)discussions.microsoft.com> wrote in message news:37838F7D-4262-4C32-A913-AC4EC872987F(a)microsoft.com... > 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
From: Rick Rothstein on 11 Nov 2009 14:08 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" <GarysStudent(a)discussions.microsoft.com> wrote in message news:D55450BC-6A65-4A01-A363-4FB9F60409F5(a)microsoft.com... > 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" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message >> news:uChQlYuYKHA.4012(a)TK2MSFTNGP04.phx.gbl... >> > 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" <GarysStudent(a)discussions.microsoft.com> wrote in >> > message news:EA56C9B1-0DE5-4203-A50E-2DE3EF79E51A(a)microsoft.com... >> >>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 >> > >> >> . >>
From: Peter T on 11 Nov 2009 16:57 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" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:eg7%23sHwYKHA.4140(a)TK2MSFTNGP05.phx.gbl... > You can shorten that slightly by leaving the Cells references out... > > Set rngCur = .Rows(2 & ":" & .Count) > > -- > Rick (MVP - Excel) > > > "John_John" <JohnJohn(a)discussions.microsoft.com> wrote in message > news:37838F7D-4262-4C32-A913-AC4EC872987F(a)microsoft.com... >> 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 >
From: John_John on 11 Nov 2009 19:43
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" <GarysStudent(a)discussions.microsoft.com> wrote in message > news:D55450BC-6A65-4A01-A363-4FB9F60409F5(a)microsoft.com... > > 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" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message > >> news:uChQlYuYKHA.4012(a)TK2MSFTNGP04.phx.gbl... > >> > 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" <GarysStudent(a)discussions.microsoft.com> wrote in > >> > message news:EA56C9B1-0DE5-4203-A50E-2DE3EF79E51A(a)microsoft.com... > >> >>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 > >> > > >> > >> . > >> > > . > |