Copy and Paste codes help needed

singcbl

Well-known Member
Joined
Feb 8, 2006
Messages
518
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,190,629
Messages
5,982,020
Members
439,750
Latest member
megaman777

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top