I know this is simple but finding it seems to elude me. There must be an
Excel function that will return the count of cells in a column that contain
any value. they will be filled from the top down with no blanks.

That would either be Count or CountA depending on the type of data in
the cell.
Count:
Counts the number of cells that contain numbers and numbers within the
list of arguments. Use COUNT to get the number of entries in a number
field in a range or array of numbers. Syntax COUNT(value1,value2, ...)
CountA:
Counts the number of cells that are not empty and the values within the
list of arguments. Use COUNTA to count the number of cells that contain
data in a range or array. Syntax COUNTA(value1,value2, ...)

Hope this helps

Arnold Klapheck wrote:
I know this is simple but finding it seems to elude me. There must be an
Excel function that will return the count of cells in a column that contain
any value. they will be filled from the top down with no blanks.

I have tried, . . . I should have said I need a way to use it
in vba

Range("a1").Select
Num = Row.Count ' or Rows.Count

but I end up with Num = 65536 when their are only 5 items in the column

I saw someone else's code of

Num = Cells(Rows.Count, LoopCount).End(xlUp).Row - 1

that seems to work, I use it in a loop, and it ignores header, I was
wondering if their was a simpler way.

There are a lot of ways:

Sub TryNow()
Dim num As Long

num = Range("A1", Range("A1").End(xlDown)).Rows.Count
MsgBox num

num = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox num

num = Application.CountA(Range("A:A"))
MsgBox num

End Sub

HTH,
Bernie
MS Excel MVP


"Arnold Klapheck" wrote in message
...
I have tried, . . . I should have said I need a way to use it
in vba

Range("a1").Select
Num = Row.Count ' or Rows.Count

but I end up with Num = 65536 when their are only 5 items in the column

I saw someone else's code of

Num = Cells(Rows.Count, LoopCount).End(xlUp).Row - 1

that seems to work, I use it in a loop, and it ignores header, I was
wondering if their was a simpler way.

=COUNT(range) counts cells in range that have numeric values
=COUNTA(range) counts all non-blank cells - we aware that a formula (eg
=IF(A1),A1, "") that returns a blank will be counted by COUNTA.

This message should have been posted to the Excel.Mics newsgroup - this on
is for Visual Basic questions but not to worry!

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arnold Klapheck" wrote in
message ...
I know this is simple but finding it seems to elude me. There must be an
Excel function that will return the count of cells in a column that
contain
any value. they will be filled from the top down with no blanks.

Try this

Sub mycount()
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.CountA(myRange)
MsgBox answer
End Sub

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arnold Klapheck" wrote in
message ...
I know this is simple but finding it seems to elude me. There must be an
Excel function that will return the count of cells in a column that
contain
any value. they will be filled from the top down with no blanks.

Try this
Sub mycount()
Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.CountA(myRange)
MsgBox answer
End Sub
but what if I don't know if MyRange ends at C10?

I Think I found a solution and I don't have to worry about blanks. If anyone
can think of a way to make more efficient I would be interested.

Function FindMaxRows(Optional strwrks As String) As Integer
'Returns the number of the last row with data regardless of blanks
in-between
Sheets(strwrks).Select
Dim wb As Workbook
Dim wrks As Worksheet
Set wb = Application.ActiveWorkbook
Set wrks = wb.ActiveSheet
FindMaxRows = wrks.Cells.Find(What:="*", After:=wrks.Range("A1"), _
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
End Function

Function FindMaxColumns(Optional strwrks As String) As Integer
'Returns the number of the last Column with data regardless of blanks
in-between
Sheets(strwrks).Select
Dim wb As Workbook
Dim wrks As Worksheet
Set wb = Application.ActiveWorkbook
Set wrks = wb.ActiveSheet
FindMaxColumns = wrks.Cells.Find(What:="*", After:=wrks.Range("A1"), _
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
End Function