Excel 2000 -Range

[Gerry Cornell]

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
~~~~~~~~~~~~~~~~~

 

[Jim Cone]

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" <
(e-mail address removed)>
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
~~~~~~~~~~~~~~~~~

 

[Gerry Cornell]

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
~~~~~~~~~~~~~~~~~~~

 

[Gerry Cornell]

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
~~~~~~~~~~~~~~~~~

 

[Jim Cone]

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" <[email protected]>
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
~~~~~~~~~~~~~~~~~