SUBTOTAL

[vasim]

I have attendance record of employees like

Name 01/01/2011 02/01/2011 03/01/2011 04/01/2011 05/01/2011

ABC A A A A

ABC A A A A

XYZ A A A A

XYZ A A A A

PQR A A A A


A macro copy paste this in another worksheet and then I need to (count) subtotal it. I prefer using Subtotal under Data tab to have the result. However the blank cells when copy pasted from macro are considered as a non blank cell in the count subtotal.

Name 01/01/2011 02/01/2011 03/01/2011 04/01/2011 05/01/2011

ABC A A A A

ABC A A A A

ABC Count 2 2 2 2 2

XYZ A A A A

XYZ A A A A

XYZ Count 2 2 2 2 2

PQR A A A A

PQR Count 1 1 1 1 1


Where the answer should be this

Name 01/01/2011 02/01/2011 03/01/2011 04/01/2011 05/01/2011

ABC A A A A

ABC A A A A

ABC Count 1 2 2 1 2

XYZ A A A A

XYZ A A A A

XYZ Count 2 1 1 2 2

PQR A A A A

PQR Count 0 1 1 1 1

 

[vasim]

The post above show 4 A for all, however this is incorrect (i am not able to edit it) some of them are blank.

 

[Luke M]

Can you modify your copy/paste macro to go through and clean up "blank cells"?

 

[vasim]

To make it further simple:

Cell A1 = A

Cell B1 =B

B1 = if(A1=”A”,”A”,””)

B2 = if(B1=”A”,”A”,””)

Copy B1&B2 paste special value

B3 = Counta(B1:B2)

This should give answer as 1 however the answer received is 2.

That’s because formula copy pasted is considered as non blank cells. Any solutions please

 

[vasim]

Thanks Luke for your prompt response, can you assit me for any code to clean this up.

 

[Luke M]

Can you change your formula to:

=IF(A1="A","A",1/0)

Then you could have the macro find the errors and delete them like so:

[pre]

' If no cells found, this would normally cause an error
On Error Resume Next
Range("A1:G10").SpecialCells(xlCellTypeConstants, 16).ClearContents
On Error GoTo 0

[/pre]