Problem with formula autofill code

[banjoflanjo]

Hi,

I'm currently using the following code for autofilling a formula via a macro:

Dim rng As Range
Set rng = ActiveSheet.Range("B9")
rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))

Basically, I run a standard weekly report and use a macro to automatically insert formulae, chop up and filter the data as required, and then drop the formatted info into a database. Now the above code has been working great until the last report that I ran. The last report only had one line of data on it for the week, so my macro inserted the formulae against the one line of info, and then tried to fill all of my formulae down to the very last row and caused problems with the rest of my macro from there.

Does anyone know how I can do the same as the code used above, but so that it can also recognise when there is only one line of data and so that it doesn't try to fill down to the very bottom?

Many thanks in advance!

Banjo

 

[onlyadrafter]

Hello,

how about

Dim rng As Range
    Set rng = ActiveSheet.Range("B9")
    If Range("A65536").End(xlUp).Row > 9 Then
        rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))
    End If

 

[banjoflanjo]

Thanks onlyadrafter,

That worked a treat!!

However I've now got another related problem. In my macro I use the code:

Range("CopyStart1:CopyEnd1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

This is the bit where I pick up my final data to drop it into the database. CopyStart1 and CopyEnd1 are just the start and end of the first row of data, the rest takes me to the bottom of the block of data and copies it. However, I'v now only got one row of data for the week. So when I come to move to the bottom of the data block it goes straight to row 65536.

Any ideas anyone?????