Autofill goes past Last Row, Last row identifies correct Row

[duncanjackson]

Hi

I have done lots of Google searches and found nothing on this. Normally I can find an answer on Google, or force the code to do what I want by trying a different method.

I have a VBA problem that is driving me nuts.

I'm using End(xlUp) to find my last row.

When I hover over Last row in the code it correctly identifies the last row as 10687

I then autofill formula's from my 2nd row to Last row...

... And for some reason it fills to Row 210678

Here is my bit of code:

'Copy formulas down        With Sheets("HMRC")
            LastRow = Range("G" & Rows.Count).End(xlUp).Row
        End With
    Range("A2:E2").Select
            Selection.AutoFill Destination:=Range("A2:E2" & LastRow)
    Range("L2:Y2").Select
            Selection.AutoFill Destination:=Range("L2:Y2" & LastRow)

Does anyone know why the Macro correctly identifies the last row I want to fill to, but then merrily careers past it?

I've also tried the following and the same happens with this method too:

'Declare variable
'
Dim LR As Long
'
'Copy formulas down
    Range("A2:E2").Select
        LR = Sheets("HMRC").Range("G1").CurrentRegion.Rows.Count
            Selection.AutoFill Destination:=Range("A2:E2" & LR)
    Range("L2:U2").Select
            Selection.AutoFill Destination:=Range("L2:U2" & LR)

Many thanks in advance for anyone who can help!

 

[Joe4]

Welcome to the Board!

If you are tacking on the row number manually, you need to get rid of the current row number, i.e.

:=Range("L2:U" & LR)

instead of:

:=Range("L2:U[COLOR=#ff0000]2[/COLOR]" & LR)

Otherwise, if last row was 300, you are actually going down to row 2300!
You need to update that in all those autofill places in your code.

 

[duncanjackson]

Hi Joe4

Thank you so much! you've cured it. I've been going mad trying to solve this for nearly a week and half!

That was such a simple fix. Thank you!

I love this site! I've been coming here for years and always found the answer I needed.

I am so, so grateful

 

[Joe4]

You are welcome. Glad I was able to help!