• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

count data

snjpverma

Member
I want to count the number of cells in Column A which has some data in it.

let say if the data is form A1 to A23, then I should get 23 as the answer. In case there is a blank in between, then the result might change.
My try is below:
PHP:
ActiveSheet.Column("A:A").CountA
 
Code:
Option Explicit

Sub cntColA()
Dim Columncount As Integer
Columncount = Application.WorksheetFunction.CountA([A1:A2000])
MsgBox Columncount

Sheet1.Range("B1").Value = Columncount
End Sub
 
You try below VBA route as well.

Code:
On Error Resume Next
cnt1 = Range("A:A").SpecialCells(xlCellTypeConstants).Count
cnt2 = Range("A:A").SpecialCells(xlCellTypeFormulas).Count
On Error GoTo 0
MsgBox cnt1 + cnt2
 
Back
Top