Blank cells should not be counted as a values

[Swiemel]

Hey Guys,

I've got the following issue:

Using W7 and Excel 2010, I have a data sheet containing ca. 34.000 rows. I currently have a column which calculates the difference in time between two time entries through the use of a formula.

The formula i'm currently using is:

=IF(AND(A1>Legenda!P1,A1<Legenda!P2),A1,"")<legenda!$p2),a1,"")< strong="">
<legenda!$p$2),a1,"")

</legenda!$p$2),a1,"")
</legenda!$p2),a1,"")<><legenda!$p2),a1,"")< strong=""><legenda!$p$2),a1,"")
This formula results are then copied from column B as Flat data (without formulas) as 'flat' text to a neighbouring column with the use of a Macro (Let's say Column C).

The problem occurs when i select C1:C4. The count shows 7 instead of the 6 real values in those 7 cells. After i press delete in C4 or use clear content it does what it's supposed to and counts only 3 values! The same applies when i use the data in a PT....

Ofcourse i could select every 'empty' cell and manually press delete... but i'm looking for a solution which doens't require an X amount of workarounds.
</legenda!$p$2),a1,"")
</legenda!$p2),a1,"")<><legenda!$p2),a1,"")< strong=""><legenda!$p$2),a1,"")

See the example underneath

</legenda!$p$2),a1,"")
</legenda!$p2),a1,"")<><legenda!$p2),a1,"")< strong=""><legenda!$p$2),a1,"")
I Would love to hear your opinions on how to tackle this!

Thanks in advance,

Swiemel
</legenda!$p$2),a1,"")
</legenda!$p2),a1,"")<><legenda!$p2),a1,"")< strong=""><legenda!$p$2),a1,"")


PS: i also added the macro im using to copy paste the formula results to another column!

Data example:
A	B	C
00:26:35	00:26:35	00:26:35
00:50:45	00:50:45	00:50:45
00:02:55	00:02:55	00:02:55
18:36:16	(Value is 'False' so "" is added to the cell value)
01:56:06	01:56:06	01:56:06
00:46:40	00:46:40	00:46:40
00:11:16	00:11:16	00:11:16

<tbody>
</tbody>

<tbody> </tbody>

Macro:

Sub AHT_Copy_Paste()Msg = "Wil je de AHT's plat kopiëren?" Ans = MsgBox(Msg, vbYesNo) Select Case Ans Case vbYes ' ' AHT_Copy_Paste Macro '' Range("AB2:AD13").Select ActiveWindow.ScrollRow = 2765 ActiveWindow.ScrollRow = 125706 ActiveWindow.ScrollRow = 147807 ActiveWindow.ScrollRow = 156096 ActiveWindow.ScrollRow = 161621 ActiveWindow.ScrollRow = 169909 ActiveWindow.ScrollRow = 185104 ActiveWindow.ScrollRow = 190630 ActiveWindow.ScrollRow = 196155 ActiveWindow.ScrollRow = 203062 ActiveWindow.ScrollRow = 208587 ActiveWindow.ScrollRow = 212731 ActiveWindow.ScrollRow = 216875 ActiveWindow.ScrollRow = 219638 ActiveWindow.ScrollRow = 223782 ActiveWindow.ScrollRow = 227926 ActiveWindow.ScrollRow = 232070 ActiveWindow.ScrollRow = 234833 ActiveWindow.ScrollRow = 237596 ActiveWindow.ScrollRow = 240359 ActiveWindow.ScrollRow = 241740 ActiveWindow.ScrollRow = 245884 ActiveWindow.ScrollRow = 247265 ActiveWindow.ScrollRow = 248647 ActiveWindow.ScrollRow = 252791 ActiveWindow.ScrollRow = 255554 ActiveWindow.ScrollRow = 258316 ActiveWindow.ScrollRow = 259698 ActiveWindow.ScrollRow = 262460 ActiveWindow.ScrollRow = 265223 ActiveWindow.ScrollRow = 266604 ActiveWindow.ScrollRow = 270749 ActiveWindow.ScrollRow = 272130 ActiveWindow.ScrollRow = 274893 ActiveWindow.ScrollRow = 276274 ActiveWindow.ScrollRow = 279037 ActiveWindow.ScrollRow = 281799 ActiveWindow.ScrollRow = 284562 ActiveWindow.ScrollRow = 285943 ActiveWindow.ScrollRow = 290088 ActiveWindow.ScrollRow = 295613 ActiveWindow.ScrollRow = 299757 ActiveWindow.ScrollRow = 303901 ActiveWindow.ScrollRow = 308045 ActiveWindow.ScrollRow = 313571 ActiveWindow.ScrollRow = 317715 ActiveWindow.ScrollRow = 321859 ActiveWindow.ScrollRow = 328766 ActiveWindow.ScrollRow = 337054 ActiveWindow.ScrollRow = 343961 ActiveWindow.ScrollRow = 352249 ActiveWindow.ScrollRow = 361918 ActiveWindow.ScrollRow = 370206 ActiveWindow.ScrollRow = 377113 ActiveWindow.ScrollRow = 389545 ActiveWindow.ScrollRow = 400596 ActiveWindow.ScrollRow = 410266 ActiveWindow.ScrollRow = 421317 ActiveWindow.ScrollRow = 430986 ActiveWindow.ScrollRow = 439274 ActiveWindow.ScrollRow = 450325 ActiveWindow.ScrollRow = 458613 ActiveWindow.ScrollRow = 465520 ActiveWindow.ScrollRow = 473808 ActiveWindow.ScrollRow = 480715 ActiveWindow.ScrollRow = 490385 ActiveWindow.ScrollRow = 500054 ActiveWindow.ScrollRow = 509724 ActiveWindow.ScrollRow = 520775 ActiveWindow.ScrollRow = 534588 ActiveWindow.ScrollRow = 544258 ActiveWindow.ScrollRow = 553927 ActiveWindow.ScrollRow = 563597 ActiveWindow.ScrollRow = 574648 ActiveWindow.ScrollRow = 584317 ActiveWindow.ScrollRow = 596749 ActiveWindow.ScrollRow = 607800 ActiveWindow.ScrollRow = 617470 ActiveWindow.ScrollRow = 627139 ActiveWindow.ScrollRow = 640953 ActiveWindow.ScrollRow = 652004 ActiveWindow.ScrollRow = 663055 ActiveWindow.ScrollRow = 674106 ActiveWindow.ScrollRow = 686538 ActiveWindow.ScrollRow = 697589 ActiveWindow.ScrollRow = 710021 ActiveWindow.ScrollRow = 723835 ActiveWindow.ScrollRow = 734885 ActiveWindow.ScrollRow = 748699 ActiveWindow.ScrollRow = 759750 ActiveWindow.ScrollRow = 770801 ActiveWindow.ScrollRow = 781852 ActiveWindow.ScrollRow = 794284 ActiveWindow.ScrollRow = 806716 ActiveWindow.ScrollRow = 817767 ActiveWindow.ScrollRow = 830199 ActiveWindow.ScrollRow = 839869 ActiveWindow.ScrollRow = 846776 ActiveWindow.ScrollRow = 857826 ActiveWindow.ScrollRow = 866115 ActiveWindow.ScrollRow = 874403 ActiveWindow.ScrollRow = 881310 ActiveWindow.ScrollRow = 886835 ActiveWindow.ScrollRow = 893742 ActiveWindow.ScrollRow = 897886 ActiveWindow.ScrollRow = 903411 ActiveWindow.ScrollRow = 907555 ActiveWindow.ScrollRow = 910318 ActiveWindow.ScrollRow = 913081 ActiveWindow.ScrollRow = 915844 ActiveWindow.ScrollRow = 917225 ActiveWindow.ScrollRow = 918606 ActiveWindow.ScrollRow = 919988 ActiveWindow.ScrollRow = 921369 ActiveWindow.ScrollRow = 922750 ActiveWindow.ScrollRow = 924132 ActiveWindow.ScrollRow = 925513 ActiveWindow.ScrollRow = 926894 ActiveWindow.ScrollRow = 928276 ActiveWindow.ScrollRow = 929657 ActiveWindow.ScrollRow = 931038 ActiveWindow.ScrollRow = 932420 ActiveWindow.ScrollRow = 933801 ActiveWindow.ScrollRow = 935183 ActiveWindow.ScrollRow = 936564 ActiveWindow.ScrollRow = 937945 ActiveWindow.ScrollRow = 939327 ActiveWindow.ScrollRow = 940708 ActiveWindow.ScrollRow = 942089 ActiveWindow.ScrollRow = 943471 ActiveWindow.ScrollRow = 944852 ActiveWindow.ScrollRow = 947615 ActiveWindow.ScrollRow = 948996 ActiveWindow.ScrollRow = 950378 ActiveWindow.ScrollRow = 951759 ActiveWindow.ScrollRow = 953140 ActiveWindow.ScrollRow = 954522 ActiveWindow.ScrollRow = 957284 ActiveWindow.ScrollRow = 958666 ActiveWindow.ScrollRow = 960047 ActiveWindow.ScrollRow = 961428 ActiveWindow.ScrollRow = 962810 ActiveWindow.ScrollRow = 964191 ActiveWindow.ScrollRow = 966954 ActiveWindow.ScrollRow = 971098 ActiveWindow.ScrollRow = 973861 ActiveWindow.ScrollRow = 976623 ActiveWindow.ScrollRow = 979386 ActiveWindow.ScrollRow = 982149 ActiveWindow.ScrollRow = 984912 ActiveWindow.ScrollRow = 989056 ActiveWindow.ScrollRow = 991818 ActiveWindow.ScrollRow = 994581 ActiveWindow.ScrollRow = 997344 ActiveWindow.ScrollRow = 1000106 ActiveWindow.ScrollRow = 1007013 ActiveWindow.ScrollRow = 1011157 ActiveWindow.ScrollRow = 1015301 ActiveWindow.ScrollRow = 1019446 ActiveWindow.ScrollRow = 1022208 ActiveWindow.ScrollRow = 1024971 ActiveWindow.ScrollRow = 1029115 ActiveWindow.ScrollRow = 1033259 ActiveWindow.ScrollRow = 1038785 ActiveWindow.ScrollRow = 1041547 ActiveWindow.ScrollRow = 1044310 ActiveWindow.SmallScroll Down:=150 Range("AB2:AD1044515").Select ActiveWindow.ScrollRow = 975382 ActiveWindow.ScrollRow = 949132 ActiveWindow.ScrollRow = 929791 ActiveWindow.ScrollRow = 911830 ActiveWindow.ScrollRow = 892489 ActiveWindow.ScrollRow = 874528 ActiveWindow.ScrollRow = 853805 ActiveWindow.ScrollRow = 827555 ActiveWindow.ScrollRow = 797161 ActiveWindow.ScrollRow = 765385 ActiveWindow.ScrollRow = 737754 ActiveWindow.ScrollRow = 699070 ActiveWindow.ScrollRow = 664531 ActiveWindow.ScrollRow = 629993 ActiveWindow.ScrollRow = 599598 ActiveWindow.ScrollRow = 563678 ActiveWindow.ScrollRow = 540191 ActiveWindow.ScrollRow = 509797 ActiveWindow.ScrollRow = 475258 ActiveWindow.ScrollRow = 451772 ActiveWindow.ScrollRow = 432430 ActiveWindow.ScrollRow = 414469 ActiveWindow.ScrollRow = 390983 ActiveWindow.ScrollRow = 370260 ActiveWindow.ScrollRow = 350918 ActiveWindow.ScrollRow = 332958 ActiveWindow.ScrollRow = 313616 ActiveWindow.ScrollRow = 297037 ActiveWindow.ScrollRow = 281840 ActiveWindow.ScrollRow = 266643 ActiveWindow.ScrollRow = 243156 ActiveWindow.ScrollRow = 233485 ActiveWindow.ScrollRow = 223814 ActiveWindow.ScrollRow = 209999 ActiveWindow.ScrollRow = 197565 ActiveWindow.ScrollRow = 185131 ActiveWindow.ScrollRow = 178223 ActiveWindow.ScrollRow = 169934 ActiveWindow.ScrollRow = 163026 ActiveWindow.ScrollRow = 151973 ActiveWindow.ScrollRow = 143684 ActiveWindow.ScrollRow = 135395 ActiveWindow.ScrollRow = 124342 ActiveWindow.ScrollRow = 105000 ActiveWindow.ScrollRow = 98093 ActiveWindow.ScrollRow = 73225 ActiveWindow.ScrollRow = 67698 ActiveWindow.ScrollRow = 55264 ActiveWindow.ScrollRow = 46975 ActiveWindow.ScrollRow = 42830 ActiveWindow.ScrollRow = 31778 ActiveWindow.ScrollRow = 27633 ActiveWindow.ScrollRow = 2 Selection.Copy Range("AE2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Case vbNo GoTo Quit: End Select Quit:End Sub​

<tbody>
</tbody>

</legenda!$p$2),a1,"")
</legenda!$p2),a1,"")<>

 

[C Moore]

You can replace all the scrolling and reduce the selecting to Range(Range("AB2:AD2"),Range("AB2:AD2").End(xlDown)).Select, then selection.copy and paste the way you have it if you want to see each step individually. Then follow up with Range(Range("AE2"),Range("AE2").end(xlDown).end(xltoright)).SpecialCells(xlCellTypeBlanks).ClearContents.

(I did not test this)