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

 

[Andrew Poulsom]

How about?

Sub Test()
    Dim Rng As Range
    Set Rng = Range("E17:E" & Range("E65536").End(xlUp).Row)
    MsgBox "There are " & WorksheetFunction.CountA(Rng) & " nonblank cells"
End Sub

 

[Chui Ping]

Thks Andrew.

How about if i would like to put the count result in cell B1. I've try it but cant do it. Pls advice.

Thks & bst rgds, June

 

[Nimrod]

Working off of Andrews solution..

Sub Test()
Range("B1") = WorksheetFunction.CountA(Range("E17:E" & Range("E65536").End(xlUp).Row))
End Sub

 

[Chui Ping]

Thks Nimrod.

Actually i've add the workbook in this macro. And now i would like to use VBA within excel to count the number of cells that contain data, in column E, start from row 17 to end. And put the count of the cells to the added workbook, sheet 2, cells (4,2). The code:

Sub cellsCount()

Set wb = Workbooks.Add
Set Sh = wb.Sheets(1)
Set Sh2 = wb.Sheets(2)

ThisWorkbook.Activate
Call TotMPN

End Sub
___________________________________________________________________

Sub TotMPN()
MPN = WorksheetFunction.CountA(Range("E17:E" & Range("E65536").End(xlUp).Row))
Sh2.Cells(4, 2) = MPN
Sh2.Cells(4, 2).Select
Selection.Font.Bold = True
Sh2.Cells(4, 2).EntireColumn.AutoFit

End Sub


I've run this macro and the error pop-pup: Run-time error '424', Object required. Can anybody advice me on this to fix this error.

Thks & bst rgds, June

 

[Nimrod]

The variable Sh and Sh2 were defined in one procedure but used in another . You had exceeded the scope of the variable. To do what you wanted to do your variables would need to be either global or passed to the second procedure .

To avoid the problem I've just put the 2 procedures together.

Sub TotMPN()
Set Wb = Workbooks.Add
Set Sh = Wb.Sheets(1)
Set Sh2 = Wb.Sheets(2)

ThisWorkbook.Activate


MPN = WorksheetFunction.CountA(Range("E17:E" & Range("E65536").End(xlUp).Row))
Sh2.Cells(4, 2) = MPN
Sh2.Cells(4, 2).Font.Bold = True
Sh2.Cells(4, 2).EntireColumn.AutoFit

End Sub

 

[Chui Ping]

Hi Nimrod,

Thks a lot, this work well.



Bst rgds, June

 

[Nimrod]

Gland to have been a help :wink:

 

[Chui Ping]

Thks all.

Now i would like to count in a column, how many cell which contain "Active" / equal to "Active" and return it in another workbook, sheet 2. My code as below:

Sub countActive()
Set Wb = Workbooks.Add
Set Sh = Wb.Sheets(1)
Set Sh2 = Wb.Sheets(2)

ThisWorkbook.Activate

ucount = 0
Start_Row = 17
End_Row = Findlast(5)

For i = star_Row To End_Row
If Cells(i, 9).value = "Active" Then
ucount = ucount + 1
End If
Next i

SubSh2.Cells(4, 2) = ucount

End Sub

Error pop-up when in run this macro. Can anobody advice me on this? Pls advice also the different code "contain" and "equal to".

Thks & bst rgds.

 

[Andrew Poulsom]

You can use the CountIf worksheet function, like this:

Sub countActive()
    Set Wb = Workbooks.Add
    Set Sh = Wb.Sheets(1)
    Set Sh2 = Wb.Sheets(2)
    ThisWorkbook.Activate
    Sh2.Cells(4, 2).Value = WorksheetFunction.CountIf(Range("E17:E" & Range("E65536").End(xlUp).Row), "Active")
End Sub