Changing this code ... Please advise

J

Jako

I am using this code to count the number of cells in column A that ar
NOT empty.

'-------------------------------------------------------------
Sub RowACount()
Dim rng As Range
Dim count As Long
Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
count = Application.WorksheetFunction.CountA(rng)
MsgBox count
End Sub
'------------------------------------------------------------



Which works great!!

What i want to then do is...

Count the number of cells in column C, (but i'm not sure how to chang
the code to do this), and name the Subroutine as RowCCount()

Also

I want to count the number of Empty cells in columm A and name th
Subroutine it Sub EmptyCellCount()

Many thank
 
N

Norman Jones

Hi Jako,

Try:
Sub EmptyCellCount()
Dim rng As Range
Dim MyCount As Long
Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
MyCount = rng.Cells.count - Application.WorksheetFunction.CountA(rng)
MsgBox MyCount
End Sub
Sub RowCCount()

-------------------------------

Dim rng As Range
Dim MyCount As Long
Set rng = Range(Cells(1, 3), Cells(Rows.count, 3).End(xlUp))
MyCount = Application.WorksheetFunction.CountA(rng)
MsgBox MyCount
End Sub
 
R

Ron de Bruin

Try this

Hi Jako

Change col = 1 to another number if you want a other column
If you want to use it on the column where the activecell is use this
col = ActiveCell.Column


Sub RowCountNotEmpty()
Dim rng As Range
Dim count As Long
Dim col As Integer

col = 1
With Sheets("sheet1")
Set rng = .Range(.Cells(1, col), .Cells(Rows.count, col).End(xlUp))
End With
count = Application.WorksheetFunction.CountA(rng)
MsgBox count
End Sub


Sub RowCountEmpty()
Dim rng As Range
Dim count As Long
Dim col As Integer

col = 1
With Sheets("sheet1")
Set rng = .Range(.Cells(1, col), .Cells(Rows.count, col).End(xlUp))
End With
count = rng.Cells.count - Application.WorksheetFunction.CountA(rng)
MsgBox count
End Sub
 
J

Jako

Thanks for replying so quick Norman.

The only trouble is now i have just realised that if the Empty Cells i
Column A are at the end of a block of data then it wont count them !!
(Doh!!).

I now use a routine to fill the Empty Cells with *. (An asterisk).

Could anyone please tell me how to Count how may rows contain an * i
Column A.

Many thanks again
 
J

Jako

Thanks Ron,

What would i need to do conditional counting of a range.

Using the version you posted Ron, (With Col 1).

Say i wanted a count of the number of Rows which had "AB" in colum
"V".

Also Column A is of Date format. How could i then get a count of th
Rows which had "AB" in column V between 2 dates?
eg. Between 16/07/2004 and 23/07/2004?

Could you suggest the best way to do this please.

Thankyou for your help (Again
 
N

Norman Jones

Hi Jako,

You need to provide more information.

If you want to count empty cells "at the end of a block of data " , how do
you determine the bottom boundary of these empty cells?
There must be some rule. else how would you know where to enter your
asterisks. If you can exxplain what exactly it is that you are endeavouring
to achieve, perhaps you can reach your goal more simply and expeditiously.

That said, if you want to count the number of asterisks in column A, try:

MsgBox Application.CountIf(Columns("A"), "~*")
 
J

Jako

Sorry forgot to say.

Column A for some situations is empty. A unique reference is always i
Column "C" and has also got a value even when the cells in column "A
are empty.

What i did to find the number of empty cells in column "A" was t
subtract the value of the number of rows in column "A" from the numbe
of cells in coumn "A" which then gives me the number of "Empty Cells i
column "A".

(Hope that males sense).

This is my modified code using this method although obviously Ron'
code is more professional.

Sub CompleteAuditsStats()
Set rng = Range(Cells(2, 1), Cells(Rows.count, 1).End(xlUp))
iCompletedAudits = Application.WorksheetFunction.CountA(rng)
Set rng = Range(Cells(2, 3), Cells(Rows.count, 3).End(xlUp))
iTotalCompletedAudits
Application.WorksheetFunction.CountA(rng)
iCompletedRevisitAudits = (iTotalCompletedAudits
iCompletedAudits)
'MsgBox ("All 3 :") & iCompletedAudits & iTotalCompletedAudits
iCompletedRevisitAudits
End Sub

It's not great but works ok.


But what i'd like to do now is count the number of rows with "AB" i
column "V".

And also number of rows between 2 dates which contain "ABA in colum
"V"


--------------------------------------------------------------------
Just as a side note i was going to fill the empty cells with * usin
this code:

Sub AsteriskFill()
On Error Resume Next
With Sheets("sheet1").UsedRange.Cells.SpecialCells(xlCellTypeBlanks)
.Value = "*"
End With
On Error GoTo 0
End Sub

then add the cells containing a *.

and then removing them again using this code:

Sheets("sheet1").Cells.Replace What:="~*", Replacement:=""

But obviously now i no longer need to do this except i might use thi
method so i can do a regioncopy to another worksheet more easily.

Thanks for the hel
 
J

Jako

Sorry should have checked properly before posting.

Please note this ammendment.

What i did to find the number of empty cells in column "A" was t
subtract the value of the number of rows in column "A" from the numbe
of cells in coumn "C" which then gives me the number of "Empty Cells i
column "A".

:eek
 
R

Ron de Bruin

Hi
Also Column A is of Date format. How could i then get a count of the
Rows which had "AB" in column V between 2 dates?
eg. Between 16/07/2004 and 23/07/2004?
Click to expand...


This formula on a worksheet will do this

=SUMPRODUCT((A1:A65535>=DATE(2004,7,16))*((A1:A65535<=DATE(2004,7,23))*(V1:V65535="AB")))
 
B

Bob Phillips

=COUNTIF(V:V,"*AB*")

and

=SUMPRODUCT(--(NOT(ISERROR(FIND("AB",V1:V10)))),--(A1:A10<=(--("2004/07/23")
)),--(A1:A10>=(--("2004/07/16"))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dana DeLouis

Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
count = Application.WorksheetFunction.CountA(rng)
Click to expand...

I may have misunderstood the question, but I think this returns the same
number.

col = 1
count = WorksheetFunction.CountA(Columns(col))

HTH
Dana DeLouis
 
R

Ron de Bruin

Hi Dana

Maybe the OP have a other reason to use the range
But it is easy to get the blanks now

count = rng.Cells.count - Application.WorksheetFunction.CountA(rng)
MsgBox count
 
D

Dana DeLouis

Hello. Would this idea work?

Set Rng = Columns("A:A") 'Your Range here
With WorksheetFunction
Stuff = .CountA(Rng)
Blanks = .CountBlank(Rng)
End With

Dana DeLouis
 
R

Ron de Bruin

Hi Dana

It will give you all empty cells in the column.
If the OP want that I don't know?

If I use 100 rows of data and want to know how many are empty then
I don't want a answer of 65436 or more.
 
Top