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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,215,775
Messages
6,126,828
Members
449,343
Latest member
DEWS2031

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