Dave,

You wrote "How about:".
I say "How about that!"
Your sub worked perfectly.
It will save me a lot of "dragravation".
(I have a crappy mouse.)
I can see using this routine in other spreadsheets - just set the ranges.

Could you add an UNDO feature? I Ctrl+Z a lot.

I see Toppers knows of your talent.
I appreciate his help, too.

Thanks for the sub, I'll put your name on it.

Zapper



"Dave Peterson" wrote in message
...
How about:

Option Explicit
Sub FILL2()

Dim ValidRng As Variant
Dim rng As Range
Dim i As Long

ValidRng = Array("rangeA", "rangeB", "rangeC", "rangeD")

For i = LBound(ValidRng) To UBound(ValidRng)
Set rng = Range(ValidRng(i))
If Intersect(ActiveCell, rng) Is Nothing Then
'do nothing
Else
If ActiveCell.Address = rng.Cells(rng.Cells.Count).Address Then
'in the last cell, so don't do anything
Else
ActiveCell.Copy _
Destination:=Range(ActiveCell.Offset(1, 0), _
rng.Cells(rng.Cells.Count))
End If
rng.Cells(rng.Cells.Count).Offset(1, 0).Select
Exit For
End If
Next i
End Sub

Zapper wrote:

Topper,
The sub is working like a charm on all but the last range (rangeT). It's
fast too.
It's hanging on the following line:
target.Copy target.Offset(1, 0).Resize(lr - target.Row)
I won't ask you to work on Sunday, I can wait.
Regards, Zapper

"Toppers" wrote in message
...
Zapper,
Try these changes which now COPY the cells and hence
formula.

Sub FILL()
' FILL Macro
' Macro recorded 1/19/2006 by Zapper
'
' Keyboard Short cut: Ctrl+y
'
Dim target As Range, rng As Range
Set target = ActiveCell
ValidRng = Array("rangeA", "rangeW", "rangeF", "rangeV", "rangeT")
For i = 0 To 4
Set rng = Range(ValidRng(i))
If Not Intersect(target, rng) Is Nothing Then
lr = CInt(Right(rng.Address, 2)) ' Last row in rng
target.Copy target.Offset(1, 0).Resize(lr - target.Row)
Cells(target.Row, target.Column).Select
Exit Sub
End If
Next i
End Sub

"Zapper" wrote:

I need some help with this problem.

I have four ranges in the same worksheet.
For example:

rangeA (d5:d15)
rangeB (d20:d33)
rangeC (d40:d67)
rangeD (d74:d82)

I defined four names:

rangeAend (d15)
rangeBend (d33)
rangeCend (d67)
rangeDend (d82)

I want to be able to invoke a macro (Ctrl+letter)
from any cell within any of the four ranges.
The first thing I want to do is make sure the cell is in column D
and within one of the four ranges. Then I want to FILL
from that cell to the end of the range the cell is in.
And then move to one cell below the end of the range.

I'd appreciate any and all help.






--

Dave Peterson