I have obtained help and finally come out with this codes, which basically copy and paste from the active cell down basing on a range to define the range to copy. It work as long as the active cell is on row 2 but encounter an error if the active cell is in row 1. I would appreciate your help to amend the code to overcome this problem. The code is displayed below:
Code:
Public Sub copy_formula3()
Dim FirstRow As Long
Dim LastRow As Long
Dim NumRows As Long
Dim MatchRange As Range
Dim CopyFrom As Range
Dim DataRange As Range
Dim BlankRange As Range
Set MatchRange = Application.InputBox("Select ALL of match range with the mouse", Type:=8)
If Not MatchRange Is Nothing Then
With ActiveCell
FirstRow = MatchRange.Row
LastRow = MatchRange(MatchRange.Count).Row
NumRows = LastRow - FirstRow
Set DataRange = .Offset(FirstRow - 1, 0).Resize(NumRows)
DataRange.ClearContents
Set BlankRange = Cells(FirstRow - 1, MatchRange.Column).Resize(NumRows).SpecialCells(xlCellTypeBlanks).Offset(0, .Column - MatchRange.Column)
.Copy DataRange
BlankRange.ClearContents
End With
End If
End Sub