Select EndDown, and then a further 2 rows

[dannybland]

I have the below code which selects all data below A5, includes the 17 columns on the right of it, and then offsets by one so I don't clear the title row.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">` Set wksdestination = Sheets("Continental")
Set registcell = wksdestination.Range("A5")
Set rngdata = Range(registcell.End(xlDown).Offset(0, 17), registcell.Offset(1, 0))
rngdata.Clear
`

</code>I also need it to clear the two rows below the end row as well as there are occasionally some SUM functions in the cells two below some of the columns. Can someone help with the last bit of adding the two rows below selection as well?
I tried

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">`
Set wksdestination = Sheets("Continental")
Set registcell = wksdestination.Range("A5")
Set rngdata = Range(registcell.End(xlDown).Offset(2, 17), registcell.Offset(1, 0))
rngdata.Clear
`

</code>But that gives a Run-time error '1004'

I have also tried the below, but it breaks if there is no data, and isn't ideal as it involves selecting the different sheets, would prefer if it ran without selecting each sheet, but if that's the only way then that'll have to do

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">

Set

 wksdestination = Sheets("Continental")
Set registcell = wksdestination.Range("A5")
Set rngdata = Range(registcell.End(xlDown).Offset(0, 17), registcell.Offset(1, 0))
wksdestination.Select
Range(rngdata.Offset(2, 0), rngdata).Select
Selection.Clear

</code>Thanks

 

[Andrew Poulsom]

In case there is no date below A5 you can use End(xlUp) from the bottom of the column:

Sub TTTT()
    Set wksdestination = Sheets("Continental")
    With wksdestination
        Set registcell = .Range("A5")
        Set rngdata = .Range(.Cells(.Rows.Count, 1).End(xlUp).Offset(2, 17), registcell.Offset(1, 0))
    End With
    rngdata.Clear
End Sub

 

[dannybland]

It's giving a

Compile error:
Sub or Function not defined

And highlighting my sub name, I pasted the code in exactly as you put,

Sub CopyRegs()


    Dim wksdata As Worksheet
    Dim wksdestination As Worksheet
    Set wksdata = Sheets("Motivity Data")
    Dim rngdata As Range
    Dim rngdestination As Range
    Dim registcell As Range
    
'#################################################################################################


    Set wksdestination = Sheets("Continental")
    With wksdestination
        Set registcell = .Range("A5")
        Set rngdata = .Range(.Cells(.Rows.Count, 1).End(xlUp).Offset(2, 17), registcell.Offset(1, 0))
    End With
    rngdata.Clear

 

[Andrew Poulsom]

I tested the code before posting it and it compiled and ran fine. Your error must be for another reason.