Delete columns values inbetween

[Guest]

Hi,

I have a set of dates in a columnA.
User is prompted to select a row of these rows (using Inputbox option).
Based on user's selection (suppose Row#7), the values will be copied to a
new columnB retaining First value, Last value and the selected value(row# 7).

Sl # Plan Date ColA

1 24/06/2006
2 01/07/2006
3 08/07/2006
4 15/07/2006
5 22/07/2006
6 29/07/2006
7 05/08/2006
8 12/08/2006
9 19/08/2006
10 26/08/2006
11 02/09/2006
12 09/09/2006

After user selection of row#7, the new column will have the data as below:

Sl # Plan Date ColA

1 24/06/2006
2
3
4
5
6
7 05/08/2006
8
9
10
11
12 09/09/2006

Thanks. Appreciate your help...

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim cell As Range
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set cell = Application.InputBox("select cell to copy", Type:=8)
If Not cell Is Nothing Then
Range("A1").Copy Range("B1")
cell.Copy Cells(cell.Row, "B")
Cells(iLastRow, "A").Copy Cells(iLastRow, "B")
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)