Counting non-empty cells

[Phosphonothioic]

I need to count the number of cells in a column and return that number as a variable to be use in a later function.

-The function will need to accept an input as to where to start counting.

Example: I need it to start counting at A26 vs. A1. The starting point will vary on my spreadsheet, but I already have a function that returns the row number of the particular starting point I'm looking for.

-Upon receiving that starting row from a call to a proceeding function, the function needs to output the number of non-empty cells it encounters in column A1 before it hits an empty cell. When it reachs an empty cell for the first time, it needs to end the loop and spit out the number of non-empty cells it found.

I understand the general architecture of the program I need, just am not familiar with the MS VB enough to get the coding right.


I've stumbled around a bit and put together the following:

Function Count(Number) As Integer

Dim Total
Dim nRow

Total = 0

For nRow = 2 + FindRow("Part-Time") To Worksheets("Attendance Record 0726-0801").??Columns(nCol).Rows.Count?? <--Not sure how to format.  Should be column A1 and all rows.

If Not IsEmpty(Worksheets(HOS).Cells(nRow, 1)) Then
    Total = Total + 1
    nRow = nRow + 1
Else
     End If

End Function

It horrible and not well thought out, I know. But the spirit of what I want to do it in there. I know I need a FOR loop, with the IF testing each cell to see if it's empty. Each time it finds a 'full' cell, it steps the value of Total (the variable to be used later) and steps nRow, then it needs to restart the loop.

Like I said, not good with the coding, but the general outline is there. Please help!

[Leith Ross]

Hello Phosphonothioic,

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

Use the Bulletin Board Code Tags
[code] at the start of the first line,
[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

[royUK]

What is your function supposed to do? It doesn't return a value to Cont, which should be a Long I think.