Solved: Autofill, last row in column

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]

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:

Code:

---

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

---

Hope that helps,

Mark

Hi GTO

Thanks for your explanation.

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

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]

Perfect!!

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

Thanks again

Hoopsah