Insert formula for next completely blank row

[LNG2013]

I am trying to inset a count formula in column F of the next completely blank row of a sheet to count from 1 above its location to the top of the page -2 the two very top lines (header and a blank line).

 

[Andrew Poulsom]

Can you give a sample formula please?

 

[LNG2013]

So if I were using this in a regulare sheet I would say
Range("L4").Formula = "=COUNT(F:F)"

But I need the formula Range to be the last completely blank row of column F, the Count then needs to be adjust for this as well saying to Count from starting at the row above its position to the top, 2 lines for the header and another blank line.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    With Worksheets("Sheet1")
        LastRow = .Range("F" & .Rows.Count).End(xlUp).Row
        .Range("F" & LastRow + 1).Formula = "=COUNT(F3:F" & LastRow & ")"
    End With
End Sub

Change the worksheet reference to suit.

 

[LNG2013]

Thanks Andrew that worked very well!

Is there a way to amend this same code so that it then does a count for columns G, H, I, & J, but puts the count formula each to the right of the found F field (this way they are all in the same row)?

I have attached a Workbook for reference. I replicated your code 5x, adjusting for the different columns, but it places the value to the last row of that specific column instead of the same row as the column F count.

http://dl.dropbox.com/u/550012/EndSummary.xls

 

[Andrew Poulsom]

Just expand the range:

Sub Test()
    Dim LastRow As Long
    With Worksheets("Sheet1")
        LastRow = .Range("F" & .Rows.Count).End(xlUp).Row
        .Range("F" & LastRow + 1 & ":J" & LastRow + 1).Formula = "=COUNT(F3:F" & LastRow & ")"
    End With
End Sub

 

[LNG2013]

Thanks Andrew that worked!