• 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.

VBA CountA

Siddarth

Siddarth

New Member
HI, I have to check how many non-empty cells are there in a row range, say from A1 to Z1 and then display a message in a MsgBox in VB if the count is greater then 3. Now i have the following code for the same...
Code: 
If (WorksheetFunction.CountA(Range("A1:Z1") > 3)) Then
MsgBox "Count is Greater then 3"
End If

I have this code written i the worksheet_change event.
I want the same code to be running in other row ranges as well, eg : A2:Z2, A3:Z3, A4:Z4 etc..
Please help...
Thanks in advance. :)
 
Siddarth

Firstly, Welcome to the Chandoo.org Forums

Have a try of this instead:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row < 6 Then 'Adjust value to suit maximum row
  If WorksheetFunction.CountA(Range(Cells(Target.Row, 1), Cells(Target.Row, 26))) > 3 Then
  MsgBox "Count in Row:" + Str(Target.Row) + " is Greater then 3"
  End If
  End If
End Sub
 
First of all thanks Hui for giving a prompt reply with a nice answer. :)
This code works charm [I sat for 9 hours yesterday to sort this].
But i found out that this triggers the MsgBox only if we move to the cells in the same row. For Example, if i am in the 2nd row this will trigger only when i am in the 2nd row and not when i move to the 3rd, 4th or nth row directly.
 
I'm unsure what your saying

If I put 3 values in Row 3, then put a 4th value in Row 3, The macro works if I press Enter, Tab or Down arrow ?
If I only have tow values and enter a 3rd value it doesn't trigger

can you please explain how it fails ?
 
It did work fine.. All i did was that i used just this piece of code

Code: 
If WorksheetFunction.CountA(Range(Cells(Target.Row, 1), Cells(Target.Row, 26))) > 3 Then
  MsgBox "Count in Row:" + Str(Target.Row) + " is Greater then 3"
End If

skipped the outer IF Block. It worked fine. Thanks for your help. I just learnt something new from you. :D
 
What was actually happening was that when i entered the 4th value in a row and after entering the 4th value if i use the down arrow and come directly to the next row the macro wont trigger. We had to either hit enter or use tab. The code was fine. Just tuned it according to my needs. Thanks, regards. :)
 
In Order, When a cell changes the Worksheet_Change event is fired and then depending on what you have just done the Worksheet_SelectionChange event may fire.

The Worksheet_Change event has the Target as the cell that has just changed
The Worksheet_SelectionChange event has the Target as the cell that you move to

So in this case the Worksheet_Change event is appropriate
 
@Hui, Yes i did use the "Worksheet_Change event" with your code except for the 1st IF Block. Worked Charms. I actually did not know the Range(Cells()) Property. What i was doing was this --> Range(A1:Z1), which only triggered the 1st row and not the rest. Thats where I was going wrong. Thanks for the code once again. Will see you around.. :)
 
You must log in or register to reply here.
Back
Top