Bob Phillips
12/13/2006 6:06:00 PM
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
Range("A" & LastRow + 2).Formula = "=SUM(A1:A" & LastRow & ")"
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"nospaminlich" <nospaminlich@discussions.microsoft.com> wrote in message
news:28508B1B-1383-456D-AEDD-E5DFA2969147@microsoft.com...
>I need to add a Count of non blank cells between Row 2 and the last row of
> (non contiguous) data on a worksheet 2 rows below the last row.
>
> Using the code from OzGrid:
> Dim LastRow As Long
> If WorksheetFunction.CountA(Cells) > 0 Then
>
> 'Search for any entry, by searching backwards by Rows.
> LastRow = Cells.Find(What:="*", After:=[A1], _
> SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
>
> End If
>
> returns the last row number correctly
>
> The next step is to use that to move to the relevant cell and add the
> Count
> formula e.g. something like
>
> Range("R[LastRow+2]:C1").Select
>
> should take me to Col A Row 45 where LastRow returns 43 so I can add the
> formula
>
> I've obviously got the syntax wrong here. There must be a way of using
> the
> result of LastRow to determine the cell address to select but I just can't
> see it
>
> I'd really appreciate some help with this
>
> Thanks a lot