Count number of cell in excel

[June]

Hello,
I wonder if anyone can help me determine how I might be able to use VBA within excel to count the number of cells that contain data, in column E, start from row 17 to end. I have played with this using this code:

Sub rowcount

Dim ucount As Integer
Dim rCol As Integer
Dim Start_Row As Integer
Dim End_Row As Integer
ucount = 0
rCol = 5
For i = Start_Row To End_Row
Start_Row = 17
End_Row = Findlast(5)
If Cells(i, rCol) <> "" Then
ucount = ucount + 1
End If
Next i

msgbox "There are " & ucount " no blank cell"
End Sub
_______________________________________________________________________

Function Findlast(colnum)
endFlag = 0
Cells(65536, colnum).Select
If Trim(ActiveCell.Value) = "" Then
Selection.End(xlUp).Select
Findlast = ActiveCell.Row
End If
End Function


The Run-time error '1004': Application-defined or object-defined error. I don know what wrong with the code.

Much appreciate your help.

Thks & bst rgds, June

 

[sagain2k]

Here's a procedure I've found VERY valuable that you can use for situations like this. You end up with the last ROW and Column numbers (in variables "lastrowwithdata" and "lastcolwithdata") that contain data, and can then use those to caculate the total # cells with data as needed. Hope this helps!

--Ray

Public Sub LastCellsWithData()
' Determine Last Cells (row & column number) Containing Data
' Calculates the Last Cells containing Data in the Worksheet
'
' NOTE: WORTH IT'S WEIGHT IN GOLD !!!!
' ***********************************************************************
Dim ExcelLastCell, Row, Col

' ExcelLastCell is what Excel thinks is the last cell
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

' Determine the last row with data in it (must also copy above para for this to work)
lastrowwithdata = ExcelLastCell.Row
Row = ExcelLastCell.Row

Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
Row = Row - 1
Loop
lastrowwithdata = Row ' Row number

' Determine the last column with data in it (must also copy the top para for this to work)
lastcolwithdata = ExcelLastCell.Column
Col = ExcelLastCell.Column
Do While Application.CountA(ActiveSheet.Columns(Col)) = 0 And Col <> 1
Col = Col - 1
Loop
lastcolwithdata = Col ' Column number

'MsgBox ("Last Row = " & lastrowwithdata & " Last Col = " & lastcolwithdata)

End Sub

 

[sagain2k]

I forgot to add that you need to declare the variables as Public in order to pass them on to another procecure:

Public lastcolwithdata As Integer
Public lastrowwithdata As Integer

 

[Chui Ping]

Hi all,

Thks very much. This help me a lot....



Bst rgds

 

[June]

10Q.

In code:

Sh2.Cells(4, 2).Value = WorksheetFunction.CountIf(Range("E17:E" & Range("E65536").End(xlUp).Row), "Active")

It will count all the cells which equal to "Active". How about if i would like to count all cells which contains "Active"?

Can somebody advice me the code?

Thks.....

 

[Andrew Poulsom]

That would be:

Sh2.Cells(4, 2).Value = WorksheetFunction.CountIf(Range("E17:E" & Range("E65536").End(xlUp).Row), "*Active*")