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!

[TABLE="width: 611"]
<tbody>[TR]
[TD]

Data example:
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]00:26:35
[/TD]
[TD]00:26:35
[/TD]
[TD]00:26:35
[/TD]
[/TR]
[TR]
[TD]00:50:45
[/TD]
[TD]00:50:45
[/TD]
[TD]00:50:45
[/TD]
[/TR]
[TR]
[TD]00:02:55
[/TD]
[TD]00:02:55
[/TD]
[TD]00:02:55
[/TD]
[/TR]
[TR]
[TD]18:36:16
[/TD]
[TD](Value is 'False' so "" is added to the cell value)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01:56:06
[/TD]
[TD]01:56:06
[/TD]
[TD]01:56:06
[/TD]
[/TR]
[TR]
[TD]00:46:40
[/TD]
[TD]00:46:40
[/TD]
[TD]00:46:40
[/TD]
[/TR]
[TR]
[TD]00:11:16
[/TD]
[TD]00:11:16
[/TD]
[TD]00:11:16
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 195"]
<tbody>[TR]
[TD="align: right"]
[TABLE="width: 312"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Macro:
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]

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​

[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
</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)