Changing this code ... Please advise
 

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

--
Message posted from http://www.ExcelForum.com

Changing this code ... Please advise
 

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

---
Regards,
Norman

"Jako " wrote in message
...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks


---
Message posted from http://www.ExcelForum.com/

Changing this code ... Please advise
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jako " wrote in message ...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks


---
Message posted from http://www.ExcelForum.com/

Changing this code ... Please advise
 

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

--
Message posted from http://www.ExcelForum.com

Changing this code ... Please advise
 

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

--
Message posted from http://www.ExcelForum.com

Changing this code ... Please advise
 

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"), "~*")

---
Regards,
Norman


"Jako " wrote in message
...
Thanks for replying so quick Norman.

The only trouble is now i have just realised that if the Empty Cells in
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 * in
Column A.

Many thanks again.


---
Message posted from http://www.ExcelForum.com/

Changing this code ... Please advise
 

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(xlCe llTypeBlanks)
.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

--
Message posted from http://www.ExcelForum.com

Changing this code ... Please advise
 

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

--
Message posted from http://www.ExcelForum.com

Changing this code ... Please advise
 

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?


This formula on a worksheet will do this

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jako " wrote in message ...
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 column
"V".

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?

Could you suggest the best way to do this please.

Thankyou for your help (Again)


---
Message posted from http://www.ExcelForum.com/

Changing this code ... Please advise
 

=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)

"Jako " wrote in message
...
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 column
"V".

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?

Could you suggest the best way to do this please.

Thankyou for your help (Again)


---
Message posted from http://www.ExcelForum.com/

Changing this code ... Please advise
 

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

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

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

HTH
Dana DeLouis


"Jako " wrote in message
...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks

Changing this code ... Please advise
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dana DeLouis" wrote in message ...
Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
count = Application.WorksheetFunction.CountA(rng)

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

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

HTH
Dana DeLouis


"Jako " wrote in message
...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks

Changing this code ... Please advise
 

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


"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dana DeLouis" wrote in message
...
Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
count = Application.WorksheetFunction.CountA(rng)

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

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

HTH
Dana DeLouis


"Jako " wrote in message
...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks

Changing this code ... Please advise
 

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.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dana DeLouis" wrote in message ...
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


"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dana DeLouis" wrote in message
...
Set rng = Range(Cells(1, 1), Cells(Rows.count, 1).End(xlUp))
count = Application.WorksheetFunction.CountA(rng)

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

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

HTH
Dana DeLouis


"Jako " wrote in message
...
I am using this code to count the number of cells in column A that are
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 change
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 the
Subroutine it Sub EmptyCellCount()

Many thanks