xldown with one row

[RuinAerlin]

Morning/Evening all,

What I am trying to do;
From one tab copy a list starting from c2 to last filled cell, pasting onto another tab, so the autofill after can work.

The part I am having a little difficulty with is when "C2" is the only row with data, this creates an error and stops the rest of the macro running (this is just a section of the entire macro, it is quite long and not really necessary for my question). Is there any way I can say "if further rows blank just copy C2"

Sorry just starting my foray into VBA so I know the below isn't the best coding in the world

Range("c2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("a9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("b9:k9").Select
    Lastrow& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("b9:k9").AutoFill Destination:=Range("b9:k" & Lastrow)
    On Error Resume Next

 

[MrJoosten]

Hi,

Change:

Range("c2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

into:

Dim lr as long
lr = Cells(Rows.Count, 3).End(xlUp).Row
Range("C2:C" & lr).Copy

 

[RuinAerlin]

I'm now getting the error at the next step, dropping formulas to any further rows if there is only one row....any ideas on how to resolve this? I thought the on error resume next would resolve this, but apparently not.