WOW DAVE!!
you are a genius!!

the only thing I'm wondering is why does it only work for my firs column? for
instance, I have 4 columns, C,D,E,F and your code only works when I use it in
C. Else I get this error:

"Autofill method of Range Class Failed" and it points to the last line of the
code: Rng.AutoFill Destination:=.Range(Rng, LastCell), Type:=xlFillCopy

Thank you so much again.

Dave Peterson wrote:
If you select the range to copy and then rightclick on that autofill button
(bottom right corner of selection) and drag it down as far as you need, then
you'll see an option to Copy Cells.

When you do it manually, you know when to stop.

The code has to know when to stop. I used the column to the left--unless you're
in column A. Then I used column B.

Option Explicit
Sub Testme01()

Dim Rng As Range
Dim LastCell As Range
Dim LastRow As Long
Dim OffsetCol As Long

With ActiveSheet
Set Rng = Selection.Columns(1)
If Rng.Column 1 Then
OffsetCol = Rng.Column - 1
Else
OffsetCol = Rng.Column + 1
End If
LastRow = .Cells(.Rows.Count, OffsetCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, Rng.Column)

Rng.AutoFill _
Destination:=.Range(Rng, LastCell), Type:=xlFillCopy
End With
End Sub

Hello,
I'm not even sure if what I want to do is possible but here's the scenario:
[quoted text clipped - 9 lines]

Thank you.: