Drag Handle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is their anyway you can set the drag handle to cover a
set distance each time
i.e I want to cover 143 cell downward and 6 cells in
width, can you set it so a double click etc will cover
this everytime?

Thank You

Ian
 
Ian,

You can't modify the fill handle to do that. But you can use the before
double-click event instead of the fill handle: copy the code below,
right-click on the sheet tab, select "View Code" and paste the code in the
window that appears.

You can restrict the event to a specific cell, shown for cell B3. To use
the event for any cell, remove the IF and End If lines. To restrict the
event to multiple cell, change the If statement to

If Not Intersect(Target, Range("A1:B3")) Is Nothing Then

for, of course, use with range A1:B3.

To activate the code, double click in the cell that you want to copy: you
will need to hit esc to get out of edit mode when you are done.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
Target.Copy Target.Resize(143, 6)
Application.EnableEvents = True
End If
End Sub
 
Back
Top