Counting Occupied cells

**ohaganli** · 10-16-2012, 10:26 AM

Hey everyone, im wondering how i can count how many cells in a certain collumn are occupied.
for example, in Collumn A I have a list of phone numbers that begin with 519, and in collumn B i have a list of phone numbers that begin with 416. I need code that can count how many cells in collumn A are occupied (to see how many 519 phone numbers there are) and have that number displayed in M1.

Thanks :D

**Pete_UK** · 10-16-2012, 10:31 AM

Try this:

=COUNTA(A:A)

Hope this helps.

Pete

**JDVBabu** · 10-16-2012, 10:35 AM

Could you provide a dummy excel?

Babu

**ohaganli** · 10-16-2012, 10:40 AM

okay im not quite sure how to implement this (I am very new to VB) would i put:

Range(G330).Value = Count("A:A")

(to display the count in cell G330)

**ohaganli** · 10-16-2012, 10:47 AM

This is my code that sorts my imported data and prints all the numbers with a "519" area code to collumn A on another sheet:

Sub MoveNumbers905()
Dim K As Long
K = 1
Dim s1 As Worksheet, s2 As Worksheet
Set s3 = Sheets("Sheet3")
Set s2 = Sheets("Sheet2")
Dim rG As Range
Set rG = Intersect(s2.Range("G:G"), s2.UsedRange)
Dim r As Range
Dim st As String
st = "(905)"
For Each r In rG
    If InStr(r.Value, st) > 0 Then
        s3.Cells(K, 11).Value = r.Value
        K = K + 1
    End If
Next
End Sub

So after using this code, I have collumn A filled up with all the 519 numbers from my imported data. Now i want to count the number of cells that are occupied and display that number in cell G330 by pushing a button

**Pete_UK** · 10-16-2012, 10:52 AM

You posted in the General Forum, and you didn't say anything about VBA or a macro in post #1, so I gave you a formula solution - put the formula in M1.

Hope this helps.

Pete

**Cutter** · 10-17-2012, 08:45 AM

@ ohaganli

Welcome to the forum.

Please notice that [CODE] tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.