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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,215,006
Messages
6,122,665
Members
449,091
Latest member
peppernaut

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
Back
Top