Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 -Range
If the Range is $E$10: $E$20 and named Fred how does one select the
Range omitting the first cell in the range. If that can be done how does one omit the first and last cell in the range. The reason for asking is that I am trying to avoid breaking the macro when it is necessary to insert or delete rows. I wish to insert information in the cells excluding the first and last. TIA -- ~~~~ Gerry ~~~~~~~~ Enquire, plan and execute. Stourport, England ~~~~~~~~~~~~~~~~~ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 -Range
Gerry,
Range("Fred").Offset(1, 0) ....moves the entire range reference down on row. With Range("Fred") .Offset(1, 0).Resize(.Rows.Count - 2, 1) End with ....moves the entire range reference down one row and removes the bottom two rows from the reference. You end up with a range reference of E11:E19. Note that the code does not change "Fred", but provides a reference to a different range. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gerry Cornell" < wrote in message If the Range is $E$10: $E$20 and named Fred how does one select the Range omitting the first cell in the range. If that can be done how does one omit the first and last cell in the range. The reason for asking is that I am trying to avoid breaking the macro when it is necessary to insert or delete rows. I wish to insert information in the cells excluding the first and last. TIA -- ~~~~ Gerry ~~~~~~~~ Enquire, plan and execute. Stourport, England ~~~~~~~~~~~~~~~~~ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 -Range
Thanks Jim that seems to do the trick.
I am fed up with the tedious task of manually updating spreadsheets every month so I expect I shall be back for more advice soon <G. My wife and I much enjoyed a visit to San Francisco in 2001. Of course it can be quite disconcerting when visiting Fisherman's Wharf to be mistaken by your wife for a local resident! -- Thanks again. Gerry ~~~~ FCA Stourport, England Enquire, plan and execute ~~~~~~~~~~~~~~~~~~~ Jim Cone wrote: Gerry, Range("Fred").Offset(1, 0) ...moves the entire range reference down on row. With Range("Fred") .Offset(1, 0).Resize(.Rows.Count - 2, 1) End with ...moves the entire range reference down one row and removes the bottom two rows from the reference. You end up with a range reference of E11:E19. Note that the code does not change "Fred", but provides a reference to a different range. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gerry Cornell" < wrote in message If the Range is $E$10: $E$20 and named Fred how does one select the Range omitting the first cell in the range. If that can be done how does one omit the first and last cell in the range. The reason for asking is that I am trying to avoid breaking the macro when it is necessary to insert or delete rows. I wish to insert information in the cells excluding the first and last. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 -Range
Jim or any other kind soul who can help Trying to modify Jim's suggestion has hit problems Range("RANGEMAT").Select Range ("RANGEMAT").Offset(1,0).Resize(Rows.Count - 2, 1) Selection.Replace What:="", Replacement:="Check", LookAt:= _ xlPart, SearchOrder:=xlByColumns, MatchCase:=False Each time I try to correct Line 2 it throws up another problem. I haven't got as far as testing Line 3 and 4. Line 2 is intended to omit the first and last cell in the Range "RANGEMAT" and line 3 and 4 to enter the word Check in each cell in RANGEMAT except the first and last cells. TIA -- ~~~~ Gerry ~~~~~~~~ Enquire, plan and execute. Stourport, England ~~~~~~~~~~~~~~~~~ "Gerry Cornell" wrote in message ... Thanks Jim that seems to do the trick. I am fed up with the tedious task of manually updating spreadsheets every month so I expect I shall be back for more advice soon <G. My wife and I much enjoyed a visit to San Francisco in 2001. Of course it can be quite disconcerting when visiting Fisherman's Wharf to be mistaken by your wife for a local resident! -- Thanks again. Gerry ~~~~ FCA Stourport, England Enquire, plan and execute ~~~~~~~~~~~~~~~~~~~ Jim Cone wrote: Gerry, Range("Fred").Offset(1, 0) ...moves the entire range reference down on row. With Range("Fred") .Offset(1, 0).Resize(.Rows.Count - 2, 1) End with ...moves the entire range reference down one row and removes the bottom two rows from the reference. You end up with a range reference of E11:E19. Note that the code does not change "Fred", but provides a reference to a different range. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gerry Cornell" < wrote in message If the Range is $E$10: $E$20 and named Fred how does one select the Range omitting the first cell in the range. If that can be done how does one omit the first and last cell in the range. The reason for asking is that I am trying to avoid breaking the macro when it is necessary to insert or delete rows. I wish to insert information in the cells excluding the first and last. TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 -Range
Those little dots are important. Close doesn't count. <g "Rows.Count" returns the number of rows on the worksheet. "Range("RANGEMAT").Rows.Count" returns the number of rows in the range. "Replace" won't work on empty cells. The first example uses an "x" as the item to replace. The second example just adds the text to the cells... '------------------ Sub FirstTry() With Range("RANGEMAT") .Offset(1, 0).Resize(.Rows.Count - 2, 1).Replace What:="x", _ Replacement:="Check", LookAt:=xlPart, _ SearchOrder:=xlByColumns, MatchCase:=False End With End Sub '-------------------- Sub SecondTry() With Range("RANGEMAT") .Offset(1, 0).Resize(.Rows.Count - 2, 1).Value = "Check" End With End Sub ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gerry Cornell" wrote in message Jim or any other kind soul who can help Trying to modify Jim's suggestion has hit problems Range("RANGEMAT").Select Range ("RANGEMAT").Offset(1,0).Resize(Rows.Count - 2, 1) Selection.Replace What:="", Replacement:="Check", LookAt:= _ xlPart, SearchOrder:=xlByColumns, MatchCase:=False Each time I try to correct Line 2 it throws up another problem. I haven't got as far as testing Line 3 and 4. Line 2 is intended to omit the first and last cell in the Range "RANGEMAT" and line 3 and 4 to enter the word Check in each cell in RANGEMAT except the first and last cells. TIA -- ~~~~ Gerry ~~~~~~~~ Enquire, plan and execute. Stourport, England ~~~~~~~~~~~~~~~~~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format range set-up in Excel 2000 | Excel Worksheet Functions | |||
allow users to edit a range in excel 2000 | Excel Discussion (Misc queries) | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming | |||
Excel 2000 VBA - Selecting populated range | Excel Programming | |||
SOS:Know Deleted/Inserted Range in Excel 97/2000 | Excel Programming |