count data

[snjpverma]

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:

ActiveSheet.Column("A:A").CountA

 

[Logit]

Paste this formula in any cell other than Column A :

=COUNTA(A:A)

 

[snjpverma]

Paste this formula in any cell other than Column A :

=COUNTA(A:A)

I was looking for a way to do this with VBA.

 

[Logit]

Option Explicit

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

Sheet1.Range("B1").Value = Columncount
End Sub

 

[shrivallabha]

You try below VBA route as well.

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

 

[Marc L]

I was looking for a way to do this with VBA.

MsgBox [COUNTA(A:A)] …​

​