Solved: Autofill, last row in column

[Hoopsah]

Hi

I am trying to autofill a column to go down to the last row of data.

However, when I run this procedure it fills an extra 2 lines with the formula and I really can't see what I am missing.

The formula is in cell J5 and it is a simple NETWORKDAYS formula.

Could someone have a look at my code and see what I am neglecting to do.

Any responses welcomed.

[VBA]Public Sub SubmitDetails()
'
' SubmitDetails Macro
' Macro recorded 11/11/2010 by Gerry McNally
'
Dim pos As Long
Dim NextRow As Long
Dim LastRow As Long

On Error Resume Next
pos = Application.Match(Range("G11").Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0

With Worksheets("Input Sheet")

If pos > 0 Then

.Cells(pos, "A").Value = Range("D14").Value
.Cells(pos, "C").Value = Range("D17").Value
.Cells(pos, "D").Value = Range("G17").Value
.Cells(pos, "E").Value = Range("J17").Value
.Cells(pos, "F").Value = Range("D20").Value
.Cells(pos, "G").Value = Range("G20").Value
.Cells(pos, "H").Value = Range("D22").Value
.Cells(pos, "I").Value = Range("D26").Value
.Cells(pos, "K").Value = Range("H26").Value
.Cells(pos, "L").Value = Range("D28").Value
.Cells(pos, "M").Value = Range("H28").Value

Else

NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(NextRow, "A").Value = Range("D14").Value
.Cells(NextRow, "B").Value = Range("G11").Value
.Cells(NextRow, "C").Value = Range("D17").Value
.Cells(NextRow, "D").Value = Range("G17").Value
.Cells(NextRow, "E").Value = Range("J17").Value
.Cells(NextRow, "F").Value = Range("D20").Value
.Cells(NextRow, "G").Value = Range("G20").Value
.Cells(NextRow, "H").Value = Range("D22").Value
.Cells(NextRow, "I").Value = Range("D26").Value
.Cells(NextRow, "K").Value = Range("H26").Value
.Cells(NextRow, "L").Value = Range("D28").Value
.Cells(NextRow, "M").Value = Range("H28").Value
.Range("J5").AutoFill .Range("J5").Resize(LastRow - 1)




End If
End With
ActiveSheet.Unprotect
Application.Run "ClearScreen"
ActiveSheet.Protect
End Sub[/VBA]

[GTO]

Hi Gerry,

I believe the problem is using .Resize is conjunction with LastRow. Let's say the data in Col B ended at row 30. .Resize(ing) to LastRow would put us to row 34. Try:

            '.Range("J5").AutoFill .Range("J5").Resize(LastRow - 1)
            .Range("J5").AutoFill .Range("J5:J" & LastRow)

Hope that helps,

Mark

[Hoopsah]

Hi GTO

Thanks for your explanation.

The code you gave me autofills down the column but stops at the line above the last line ???

[frank_m]

Hi Gerry,

Looks like you are determining the last row then adding a new row of data. which would leave you one short.

Try changing:
[VBA]Else

NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(NextRow, "A").Value = Range("D14").Value
.Cells(NextRow, "B").Value = Range("G11").Value
.Cells(NextRow, "C").Value = Range("D17").Value
.Cells(NextRow, "D").Value = Range("G17").Value
.Cells(NextRow, "E").Value = Range("J17").Value
.Cells(NextRow, "F").Value = Range("D20").Value
.Cells(NextRow, "G").Value = Range("G20").Value
.Cells(NextRow, "H").Value = Range("D22").Value
.Cells(NextRow, "I").Value = Range("D26").Value
.Cells(NextRow, "K").Value = Range("H26").Value
.Cells(NextRow, "L").Value = Range("D28").Value
.Cells(NextRow, "M").Value = Range("H28").Value
.Range("J5").AutoFill .Range("J5:J" & LastRow)[/VBA]
To:
[VBA]Else

NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = Range("D14").Value
.Cells(NextRow, "B").Value = Range("G11").Value
.Cells(NextRow, "C").Value = Range("D17").Value
.Cells(NextRow, "D").Value = Range("G17").Value
.Cells(NextRow, "E").Value = Range("J17").Value
.Cells(NextRow, "F").Value = Range("D20").Value
.Cells(NextRow, "G").Value = Range("G20").Value
.Cells(NextRow, "H").Value = Range("D22").Value
.Cells(NextRow, "I").Value = Range("D26").Value
.Cells(NextRow, "K").Value = Range("H26").Value
.Cells(NextRow, "L").Value = Range("D28").Value
.Cells(NextRow, "M").Value = Range("H28").Value
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("J5").AutoFill .Range("J5:J" & LastRow)[/VBA]

[Hoopsah]

Perfect!!

Thanks for your help guys and you explanations. I see what I was doing wrong now

Thanks again

Hoopsah